CODEWITHSUNDEEP
pythonsqlsqliteselect
Benjamin McDowell
Benjamin McDowell

Reputation: 308

SQLite3 - cross table SELECT queries

What I would like returned is all the seat_ids in the performance table that have a booking_id that matches all the booking_ids where night = 1 in the booking table - is an INNER JOIN the best way to do it?

Or is it more along the lines of """SELECT seat_id FROM performance WHERE booking_id=(SELECT * FROM booking WHERE night = ?""", (night_number))

With the above I get sqlite3.OperationalError: incomplete input error.

connection = sqlite3.connect('collyers_booking_system.db')
cursor = connection.cursor()

cursor.execute(booking_table)
cursor.execute(performance_table)

connection.commit()

booking_table = """CREATE TABLE IF NOT EXISTS
booking(
booking_id TEXT PRIMARY KEY,
customer_id INTEGER,
night INTEGER,
cost REAL,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
)"""

performance_table = """CREATE TABLE IF NOT EXISTS
performance(
performance_id TEXT PRIMARY KEY,
seat_id TEXT,
booking_id INTEGER,
FOREIGN KEY (seat_id) REFERENCES seat(seat_id),
FOREIGN KEY (booking_id) REFERENCES booking(booking_id),
)"""

night_number = 1

cursor.execute("""SELECT seat_id FROM performance INNER JOIN booking ON night=?""", (night_number))
booked_seats = cursor.fetchall()

print(booked_seats)

With this I get ValueError: parameters are of unsupported type error.

Upvotes: 0

Views: 184

Answers (2)

forpas
forpas

Reputation: 164214

First, if this is your actual code, there is a typo in the CREATE statement of the table performance.
You must remove the , at the end of:

FOREIGN KEY (booking_id) REFERENCES booking(booking_id),

Then, here:

cursor.execute("""SELECT seat_id FROM performance WHERE booking_id=(SELECT * FROM booking WHERE night = ?""", (night_number))

you missed a closing parenthesis for the sql statement and the subquery may return more than 1 rows, so instead of = you should use IN.
Also, the parameter night_number should passed as a tuple and not just a number, by adding a , inside the paraentheses:

cursor.execute("""SELECT seat_id FROM performance WHERE booking_id IN (SELECT * FROM booking WHERE night = ?)""", (night_number,))

For the join you need a proper ON clause, that links the tables and a , to create the tuple for night_number:

sql = """
SELECT p.seat_id 
FROM performance p INNER JOIN booking b 
ON b.booking_id = p. booking_id
WHERE b.night=?
"""
cursor.execute(sql, (night_number,))

Both ways, the operator IN and the join will work.
There is another option which sometimes performs better and this is EXISTS:

sql = """
SELECT p.seat_id 
FROM performance p 
WHERE EXISTS (
  SELECT 1 FROM booking b 
  WHERE b.night=? AND b.booking_id = p.booking_id
)
"""
cursor.execute(sql, (night_number,))

Upvotes: 1

DEV
DEV

Reputation: 1736

You are comparing a list result with an integer.

this SELECT * FROM booking WHERE night = ? => returns an N rows and you are wating for an Integer SELECT seat_id FROM performance WHERE booking_id=?.

You have to use something like this :

SELECT seat_id FROM performance WHERE booking_id in (SELECT * FROM booking WHERE night = ?""", (night_number))

Upvotes: 0

Related Questions