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

Question

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?

What should happen when there is a company with 1,2,3 and 4 item numbers, will that company be part of result ? — Pரதீப், Jan 16 '18 at 13:30
yes... because that company has all the items (1,2,3 in this example) we search for. I want to load all the companies which are having all the search items. — user2837480, Jan 16 '18 at 13:32
Possible duplicate of [SQL Server - select rows that match all items in a list](https://stackoverflow.com/questions/15977126/sql-server-select-rows-that-match-all-items-in-a-list) — Salman A, Jan 16 '18 at 20:07

score 7 · Accepted Answer · answered Jan 16 '18 at 13:24

7

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;

answered Jan 16 '18 at 13:24

Gordon Linoff

1,242,037
58
646
786

could you please explain me "count(distinct item_number) = 3;" here? why 3 is hard coded. – user2837480 Jan 16 '18 at 13:27
@user2837480 : In this query you first get all the ***rows*** that have an `item_number` of 1, 2 or 3, then `GROUP` all those rows together by `company_id`. In your sample data `company_id = 1` will have 3 such matching rows, and `company_id = 2` will have just 1 such matching row. `HAVING COUNT(DISTINCT item_number)` is like a second `WHERE` applied after the `GROUP BY`. This one counts how many distinct item_numbers each company_id has *(distinct is used in case a company the same item_number more than once)*. The 3 is hard coded because you're searching for 3 different item_numbers. – MatBailie Jan 16 '18 at 13:47
@user2837480 . . . The `count(distinct)` ensures that all three values are there for each company. The "3" is the number of values you are looking for. – Gordon Linoff Jan 16 '18 at 13:57

Pரதீப் · Answer 2 · 2018-01-16T13:49:31.460

1

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;

edited Jan 16 '18 at 13:49

answered Jan 16 '18 at 13:28

Pரதீப்

91,748
19
131
172

Necessitates a full table scan, which isn't always necessary. Sometimes the full table scan happens anyway, but @GordonLinoff's answer there is at least the possibility that it won't happen. – MatBailie Jan 16 '18 at 13:41
Shouldn't this be: count(case when item_number = 1 then 1 end) > 0 and... – JBrooks Jan 16 '18 at 13:49
actually im sending required item numbers in temp table. I have concern like im returning around 20 amount of columns data. So put them all in a group by section will be a mess. You have any other solution? Thanks for helping! – user2837480 Jan 16 '18 at 13:50
@user2837480 - Please update you question with a concrete example? Including input data and expected results? – MatBailie Jan 16 '18 at 14:04

score 1 · Answer 3 · answered Jan 16 '18 at 13:52

1

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

answered Jan 16 '18 at 13:52

JBrooks

9,901
2
28
32

At present the `HAVING` clause does the same job as the `WHERE` clause, as well as some extra necessary work. That makes the `WHERE` clause redundant, or the `HAVING` clause overly complex. I would recommend either removing the `WHERE` clause or changing the `HAVING` clause to be `COUNT(DISTINCT item_number) = 3` – MatBailie Jan 16 '18 at 13:59

score 0 · Answer 4 · answered Jan 16 '18 at 13:59

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)

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

4 Answers4