Search code examples
mysqldatabasejoininner-join

Multiple inner joins in select statement return multiple records, but it should return only one

My select statement returns a lot of records but it should only return one. I am using a lot of inner joins but I'm doing something wrong.

I am making a cinema movie reservation website and for this I made a database with these tables:

https://i.sstatic.net/cUuuX.jpg

One room contains rows A - F and seats 1 - 10 just like in this picture: https://i.sstatic.net/KpXOa.jpg

and I filled the tables show, movie, room, person, row and seat up with records so I could make a reservation. I made a few reservations which look like this:

https://i.sstatic.net/bU8T4.jpg

I made an extra page where the customer can submit his reservation ID and get information about his reservation.

So what I am trying to get is the row_letter and seatnumber with a select statement with the help of the reservation ID from the customer.

I tried to get it with this query.

SELECT seatnumber, row.row_letter FROM show 
INNER JOIN room ON room.idroom = show.room_idroom 
INNER JOIN row ON room.idroom = row.room_idroom 
INNER JOIN seat ON row.idrow = seat.row_idrow 
INNER JOIN reservation ON reservation.show_idshow = show.idshow 
WHERE reservation.idreservation = 11;

What I am getting is this: https://i.sstatic.net/nTIML.jpg

What I expected was just one record with the seatnumber and the row_letter. What am I doing wrong? Sorry for being a beginner in SQL.

Solution

  • According to the reservation table of your first image, your reservations are not in any way tied to seats, just to persons.

    You need to have another table which ties reservation to seat IDs. You could, of course, just add seat id column to reservation table, but obviously this would create issues when a person reserved more than 1 seat.

    Note also that although your other tables seem to be quite correct, they are unwieldy for the kind of of queries you seem to be doing on them, this is evidenced by the larger number of inner joins necessary. It might make sense to lose the rows table and to simply identify each seat with a row column instead.

    Once you add a reserved seats table, which could be something as simple as:

    Idreservation,
Idseat

    Then you can get the seats for reservation by:

    Select rowletter, seat_number from seats
Where idseat in (select idseat from reserved_seats where idreservation=the reservation you are checking now)

    This is assuming you moved rowletter to seats table.