-4
id   item_name  cust_id 
1     Rolex      5        
2     Diamond    33 
3     Hublop     1         
4     Ring       9          
5     Ruby       13 
6     Rolex      33 
7     Hublop     29 
8     Ring       17 
9     Belt       21 
10    Diamond     9       
12    Belt       33
13    Hublop      9                                                                                                                                                           
14    Brooch      9

From these table I have to write a query to find those cust_id who have buy all three 'diamond,brooch,ring' without using intersection ( IN clause will also not work)

Ashish Sharma
  • 9
  • 3
  • 3
    Please only tag a single RDBMS. – Dale K Jan 27 '22 at 06:56
  • And please explain why you have imposed the restrictions you have. – Dale K Jan 27 '22 at 06:57
  • And show your desired results. I've removed the conflicting RDBMS tags... feel free to add back the correct tag. – Dale K Jan 27 '22 at 07:07
  • in mysql its possible using `GROUP_CONCAT` and `FIND_IN_SET` but if you have 3 different dbms that's 3 different answers – Garr Godfrey Jan 27 '22 at 07:07
  • Do you need to find the customers that bought **exactly** those three products (so nothing else) or those that bought _at least_ those three products (but could have bought others as well). –  Jan 27 '22 at 07:09
  • output - 9 (cust_id of that customer who buy all three items i.e Ring ,Diamond, Brooch) – Ashish Sharma Jan 27 '22 at 07:11
  • 1
    This is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. – philipxy Jan 27 '22 at 07:25

1 Answers1

1

You can use sum with a case statement for each item to get how many of that item the client has bought, then filter only customers that have bought at least one of each type of product:

select cust_id from
(select cust_id,
  sum(case when item_name = 'Diamond' then 1 else 0 end) as cntDiamond,
  sum(case when item_name = 'Brooch' then 1 else 0 end) as cntBrooch,
  sum(case when item_name = 'Ring' then 1 else 0 end) as cntRing
from table_name
group by cust_id) as t
where cntDiamond > 0 and cntBrooch > 0 and cntRing > 0

Fiddle

Zakaria
  • 4,715
  • 2
  • 5
  • 31