CODEWITHSUNDEEP
sqlsql-server
RaceBase
RaceBase

Reputation: 18848

SQL Select item which has same value in all rows

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?

Upvotes: 0

Views: 596

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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');

Upvotes: 0

akshay
akshay

Reputation: 777

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

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37337

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')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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;

Upvotes: 2

Related Questions