Selecting customers with multiple purchases-CodePudding

teble relationship I have 4 tables for keeping track of purchases by clients. My goal is to select the names of clients that purchased 2 specific products (e.g. book and pencils)

The query which I thought of (and which is obviously incorrect) is:

    FROM customer c
join purchase p on c.customer_id = p.customer_id
join product pr on p.product_id = pr.product_id
WHERE EXISTS (
    SELECT 1
    FROM Purchase
    WHERE p.customer_id = c.customer_id
        AND pr.product_name = 'Book')
AND EXISTS (
    SELECT 1
    FROM Purchase
    WHERE p.customer_id = c.customer_id
        AND pr.product_name = 'Pencils')

Which return nothing, when I know that there is at least 1 customer than fits the criteria. Thank you in advance!

CodePudding user response：

You can use in, group by, and having to fetch all customer_id which bought both pencil and book. The having is there to ensure both items actually bought.

 with
     relevant_purchases as
         (
           select customer_id
           from purchase pu
           left join product pr
           on pu.product_id = pr.product_id
           where product_name in ('book', 'pencil')
           group by customer_id
           having count(distinct pu.product_id) = 2
         )
 select *
 from customer c
 where exists (
       select *
       from relevant_purchases rp
       where c.customer_id = rp.customer_id
     );

Fiddle: https://www.db-fiddle.com/f/vdpUWSse7gcLi1CmnNrRgs/1