Query 2 tables without a join

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?

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

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