CODEWITHSUNDEEP
sql
user2191145
user2191145

Reputation: 63

SQL Negative query

I have three tables, I'm presenting simplified versions of the same

Customer_dim

[Cust_id] [Cust_Name]

Product_dim

[Prod_id] [Prod_Name]

Orders_fact

[ord_id] [Cust_id] [Prod_id]

I want all the customers who have bought every product (not even a single one missing)

I want a MORE ELEGANT query than simply equating the count of each customer group with the total count of prod_dim

i.e. I DON'T want below kind of queries (since it was an interview question and there are points for elegance also)

select cust_name 
from customers c, 
   (select cust_id, count(prod_id) cnt 
    from order_fact 
    group by cust_id where cnt = (select count(prod_id) from prod_dim)) t1
where c.cust_id = t1.cust_id

Upvotes: 2

Views: 146

Answers (2)

jezzarax
jezzarax

Reputation: 413

NO count OR group by version:

SELECT Cust_Name FROM Customer_dim WHERE Cust_Id NOT IN (
    SELECT c.Cust_Id FROM Product_dim p
    CROSS JOIN Customer_dim c 
    LEFT JOIN Orders_fact o ON o.Prod_Id = p.Prod_Id AND c.Cust_Id = o.Cust_Id
    WHERE Ord_Id IS NULL
)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you used proper syntax for your query, then it might have gotten further in the interview. Here is a version of the query that should work:

select c.cust_name 
from customers c join
     (select ofa.cust_id 
      from order_fact ofa
      group by ofa.cust_id 
      having cnt = (select count(distinct prod_id) from prod_dim)
     ) ccnt
     on c.cust_id = ccnt.cust_id

Note the use of the having clause, of count(distinct) rather than count(), of proper join syntax, and of reasonable aliases.

Upvotes: 1

Related Questions