Select Records From A Table Where All Other Records With Same Foreign Key Have A Certain Value

I have a the following table ItemStatus ---------- id item_id status I want to select all item_ids where the status for every record with that item_id in the table is A. For examp

Solution 1:

select item_id
    from YourTable
    groupby item_id
    havingsum(casewhen status='A'then1else0end) =count(1)

Solution 2:

select distinct item_id
from ItemStatus
where status = 'A'and item_id notin
(select item_id
    from ItemStatus
    where status != 'A'or status isnull)

results in list of item_ids that appear as A at least once and never appear as anything else

Solution 3:

Something like this should work:

SELECTDISTINCT item_id
FROM your_table t1
WHERENOTEXISTS (
        SELECT*FROM your_table t2
        WHERE t1.item_id = t2.item_id AND t2.status <>'A'
    )

In plain English: select every item_id for which there is no row with status different from 'A'.

--- EDIT ---

Variation of Shark's idea:

SELECT item_id
FROM your_table
GROUPBY item_id
HAVINGmin(status) ='A'ANDmax(status) ='A'

This has a chance of being optimized quite nicely by the DBMS, provided you have an index on {item_id, status}. Here is the SQL Server execution plan: