All we need is an easy explanation of the problem, so here it is.
I am given the schema:
BOOKING (hotelNo, guestNo, dateFrom, dateTo)
GUEST (guestNo, guestName, guestAddress)
I an asked to formulate the query for:
List the guest numbers associated with the first name ‘Peter’ having made bookings with unknown dateTo without using an explicit or implicit joins.
My attempted query is:
(select guestNo from GUEST where guestName LIKE 'Peter') INTERSECT ALL (SELECT guestNo FROM BOOKING WHERE dateTo IS NULL);
But this does not return duplicates only at most 1 entry per guestNo, as shown in fiddle where returned values should be (1,1) rather than (1). I thought about using UNION but then this will return guestNo’s if dateTo is NULL or name is Peter, i.e. both conditions may not necessarily hold.
Database fiddle: https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17
Do you guys have any suggestions?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
In an almost literal translation from English to SQL, I would write the query as follows:
select guestNo from guest where guestName like 'Peter%' and exists ( select from booking where guestNo = guest.guestNo and dateTo is null );
NB Postgres unlike some other DBMSes doesn’t need to have any columns in the select clause! That’s ideal in the subselect here as we’re only interested in the existence of a record, not in its value).
All these queries will return 1, 1 and optimize to the same plan (for your schema and data):
SELECT guestNo FROM booking WHERE guestNo = ANY ( SELECT g.guestNo FROM guest AS g WHERE guestName = 'Peter' ) AND dateTo IS NULL
SELECT guestNo FROM booking WHERE guestNo IN ( SELECT g.guestNo FROM guest AS g WHERE guestName = 'Peter' ) AND dateTo IS NULL
SELECT guestNo FROM booking b WHERE EXISTS ( SELECT g.guestNo FROM guest AS g WHERE guestName = 'Peter' AND g.guestNo = b.guestNo ) AND dateTo IS NULL
guest.guestNo is the primary key, this is the same result and the same plan a query with the join would optimize to:
SELECT guestNo FROM booking JOIN guest USING (guestNo) WHERE guestName = 'Peter' AND dateTo IS NULL
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂