CODEWITHSUNDEEP
mysqlsql
Shyam Singh
Shyam Singh

Reputation: 123

MySQL subquery on multiple tables

I am trying to retrieve names and address of all guests with bookings for a hotel in London, alphabetically ordered by name in MySQL using subqueries and getting this Error:

Error Code: 1242. Subquery returns more than 1 row

Here's the query that I run:

select * from guest 
where guest_no = 
    (
        select guest_no 
        from booking 
        where hotel_no = (select hotel_no
                          from hotel
                          where city = 'London')
    );

and here's the schema for hotel, booking and guest:

hotel (hotel_no, hotel_name, city)

booking (hotel_no, guest_no, date_from, date_to, room_no)

guest (guest_no, g_name, g_address)

additionally, here's the schema for room:

room (room_no, hotel_no, type, price)

Please help me with the above mentioned error and possible solutions.

Thanks and regards.

Upvotes: 0

Views: 3898

Answers (4)

vhadalgi
vhadalgi

Reputation: 7189

you need joins !

try this !

select a.g_name,a.g_address from guest a inner join booking b on a.guest_no=b.guest_number inner join hotel h on b.hotel_no=h.hotel_no inner join rooms r on b.room_no=h.room_no
where h.city='London'

Upvotes: 0

G one
G one

Reputation: 2729

Change you query to

select * from guest 
where guest_no IN 
               (select guest_no from booking where hotel_no 
  IN (select hotel_no from hotel where city = 'London'));

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

why not use join as

select
g.guest_no,
g.g_name,
g.g_address
from guest g
inner join booking b on b.guest_no = g.guest_no
inner join hotel h on h.hotel_no = b.hotel_no
where h.city = 'London'

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

When you use '=', it means that the result of your subquery is exactly 1 row. If you expect multiple results, you need to use the IN keyword, like so:

select * from guest where guest_no IN (select guest_no from booking where hotel_no IN (select hotel_no from hotel where city = 'London'));

EDIT: As @flaschenpost mentions, the performance could be degraded in case there is no proper indexing on the columns involved in the subqueries. You would probably do well to use JOIN rather than such nested subqueries.

Upvotes: 0

Related Questions