SQL Select item which has same value in all rows

Question

For example, if the below is the table

SupId    ItemId          Status
1        1               Available
1        2               OOS
2        3               Available
3        4               OOS
3        5               OOS
4        6               OOS
5        7               NULL

I am looking to fetch distinct suppliers whose all items are OOS or NULL.

One solution is to get all the suppliers who has atleast one active item (active suppliers) and then add a clause NOT IN active suppliers to pick non active supplier.

Is there any better way to achieve the same?

@qxg, can you explain the issue with NOT IN vs JOIN and how the perf is taken into consideration. — RaceBase, Aug 01 '18 at 06:40
Do you want suppliers for which _all_ are `NULL` or _all_ are `OOS`? Or, do you want to restrict to just those two values? — Tim Biegeleisen, Aug 01 '18 at 06:43
Take a look at https://stackoverflow.com/questions/1200295/sql-join-vs-in-performance. Have a try of all approaches and let execution plan tell you the truth. — qxg, Aug 01 '18 at 06:48
@TimBiegeleisen suppliers for which all are NULL or all are OOS — RaceBase, Aug 01 '18 at 06:50

score 2 · Accepted Answer · answered Aug 01 '18 at 06:40

One option, using aggregation:

SELECT SupId
FROM yourTable
GROUP BY SupId
HAVING
    SUM(CASE WHEN Status = 'OOS' OR Status IS NULL THEN 1 ELSE 0 END) = COUNT(*) AND
    (MAX(Status) = 'OOS' OR COUNT(Status) = 0);

This assumes you want suppliers who have only all NULL or all OOS status. If you just want to limit to both these two status values, then use this:

SELECT SupId
FROM yourTable
GROUP BY SupId
HAVING SUM(CASE WHEN Status <> 'OOS' AND Status IS NOT NULL THEN 1 ELSE 0 END) = 0;

score 0 · Answer 2 · answered Aug 01 '18 at 06:44

0

Try:

SELECT DISTINCT SupId FROM my_table t
WHERE NOT EXISTS(SELECT 1 FROM my_table
                 WHERE SupId = t.SupId
                   AND [Status] IS  NOT NULL 
                   AND [Status] <> 'OOS')

answered Aug 01 '18 at 06:44

Michał Turczyn

32,028
14
47
69

score 0 · Answer 3 · answered Aug 01 '18 at 06:55

0

SELECT DISTINCT SupId 
FROM Table 
WHERE SupId <> (
                 SELECT DISTINCT SupId 
                 FROM Table 
                 WHERE Status NOT IN ('OOS',NULL)
               )

answered Aug 01 '18 at 06:55

akshay

777
4
15

score 0 · Answer 4 · answered Aug 01 '18 at 09:30

0

I would use NOT EXISTS :

SELECT t.*
FROM table t
WHERE NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.supid = t.supid and t1.status <> 'OOS');

answered Aug 01 '18 at 09:30

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 5 · answered Aug 01 '18 at 11:24

0

I would use group by and having:

select suppid
from t
group by suppid
having (min(Status) = 'OOS' and max(Status) = 'OOS') or
       min(Status) is null;

answered Aug 01 '18 at 11:24

Gordon Linoff

1,242,037
58
646
786

SQL Select item which has same value in all rows

5 Answers5