Not sure how to phrase my problem

Transact-SQL
1

These are the records in my table.

CID, FieldID, Value
1, 23ar45scd, Active
1, 5dre45sfg, SA
1, 7c9ui567g, 410
1, 0fre456ty, Operator
2, 23ar45scd, Active
2, 5dre45sfg, SAP
2, 7c9ui567g, 410
2, 0fre456ty, Roofer
3, 23ar45scd, Active
3, 5dre45sfg, SAP
3, 7c9ui567g, 410
4, 23ar45scd, Inactive
4, 5dre45sfg, SA
4, 7c9ui567g, 410

My select statement should return just CIDs = 3 and 4 because they don't have the FieldID = 0fre456ty.

Thanks.

2

If you need to list just the CID, then you can do this:

SELECT CID
FROM table_name
GROUP BY CID
HAVING MAX(CASE WHEN FieldID = '0fre456ty' THEN 1 ELSE 0 END) = 0

1 Like
3

I would have done this:

SELECT DISTINCT CID
FROM table_name
WHERE CID NOT IN
(
    SELECT CID FROM table_name WHERE FieldID = '0fre456ty'
)

Might be preferable if several columns were required in the SELECT (i.e. no DISTINCT and thus avoiding a GROUP BY on lots of columns)

But I have no idea what the Pros / Cons or performance benefits are, Scott's solution is slick :slight_smile:

1 Like