Query 2 tables without a join

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.

Method 1

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).

See https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17

Method 2

All these queries will return 1, 1 and optimize to the same plan (for your schema and data):

  1. SELECT  guestNo
    FROM    booking
    WHERE   guestNo = ANY
            (
            SELECT  g.guestNo
            FROM    guest AS g
            WHERE   guestName = 'Peter'
            )
            AND dateTo IS NULL
    
  2. SELECT  guestNo
    FROM    booking
    WHERE   guestNo IN
            (
            SELECT  g.guestNo
            FROM    guest AS g
            WHERE   guestName = 'Peter'
            )
            AND dateTo IS NULL
    
  3. 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
    

Because 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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply