CODEWITHSUNDEEP
sqlsql-server
user2837480
user2837480

Reputation: 369

Best SQL query to retrieve the data which has all required data

I have a transaction table with item details for each company. I want to write a query to retrieve the companies only having item numbers 1,2 and 3 (according to my sample code in below). Selected companies should have all 1,2,3 items. If some company has only item 1, then it shouldn't come. How can I write this?

CREATE TABLE #TmpTran 
(
     ID BIGINT IDENTITY, 
     COMPANY_ID BIGINT, 
     ITEM_NAME VARCHAR(50), 
     ITEM_NUMBER INT
)

INSERT INTO #TmpTran (COMPANY_ID, ITEM_NAME, ITEM_NUMBER)
VALUES (1, 'ABC', 1), (1, 'DEF', 2), (1, 'HIJ', 3),
       (2, 'KLM', 4), (2, 'KLM', 5), (2, 'ABC', 1)

How can I get only Company 1 data using WHERE or JOIN query?

Upvotes: 1

Views: 331

Answers (4)

JBrooks
JBrooks

Reputation: 10013

You said you have a lot of fields. Probably the easiest for the reader to follow would be something like:

select distinct tt.company_id
from #tmptran tt
where tt.item_number in (1, 2, 3)
and exists(select 1 
           from #tmptran ttSub 
           where ttSub.company_id = tt.company_id and ttSub.item_number = 1)
and exists(select 1 
           from #tmptran ttSub 
           where ttSub.company_id = tt.company_id and ttSub.item_number = 2)
and exists(select 1 
           from #tmptran ttSub 
           where ttSub.company_id = tt.company_id and ttSub.item_number = 3)

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

select tt.company_id
from #tmptran tt
where tt.item_number in (1, 2, 3)
group by tt.company_id
having sum(max(case tt.item_number when 1 then 1 end)) +
   and sum(max(case tt.item_number when 2 then 1 end)) +
   and sum(max(case tt.item_number when 3 then 1 end)) = 3

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Another way (more flexible approach)

select company_id
from #tmptran tt
group by company_id
having count(case when item_number = 1 then 1 end) > 0;
   and count(case when item_number = 2 then 1 end) > 0;
   and count(case when item_number = 3 then 1 end) > 0;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this with group by and having:

select company_id
from #tmptran tt
where item_number in (1, 2, 3)
group by company_id
having count(distinct item_number) = 3;

Upvotes: 7

Related Questions