want to search both tables Python SQL-CodePudding

ive made a database using python and filled a couple tables with data. i've not done sql querys for a while and cant find what im looking for online. My tables

cursor.execute("""CREATE TABLE IF NOT EXISTS Movies (
    Title varchar,
    Plot varchar,
    ReleaseDate integer,
    RunTime integer,
    Subs integer,
    Location varchar
    )""")
cursor.execute("""CREATE TABLE IF NOT EXISTS TvShows (
    Title varchar,
    Plot varchar,
    ReleaseDate varchar,
    NumberOfSeasons integer,
    RunTime integer,
    Subs Boolean,
    Location varchar
    )""")

these are the two tables im trying to search in. i want to get the title and location of every entry. i just want it in a list one after the other. e.g.

title, location
title, location 
title, location

etc till it has displayed every entry in both tables. This is what i have, it kind works but not how i want it.

query = "SELECT Movies.Title, TvShows.Title, Movies.Location, TvShows.Location FROM Movies INNER JOIN TvShows ON Movies.Location = TvShows.Location"
#"WHERE Location='F Drive'"
cursor.execute(query)
result = cursor.fetchall()
for row in result:
    print(row)

CodePudding user response：

If I read your question correctly, you want a union between the two tables, rather than a join:

query = """SELECT Title, Location FROM Movies
           UNION ALL
           SELECT Title, Location FROM TvShows"""
cursor.execute(query)
result = cursor.fetchall()
for row in result:
    print(row)

Note that introducing a computed column into the above query might work nicely. That is, we can try using this version:

SELECT Title, Location, 'Movie' AS Type FROM Movies
UNION ALL
SELECT Title, Location, 'TV show' FROM TvShows;

CodePudding user response：

The common column is Location in both the tables. The below query will work.

'''

SELECT Movies, Title, Location, 
FROM Movies 
INNER JOIN TvShows ON Movies.Location = TvShows.Location
WHERE Location='F Drive'

'''