Trying to get the query - Please help!

Transact-SQL
1

Consider the data below which is stored in temp table. How to get all those ID / State / Group combination which does not have Status record having 'AC' value ? The result should yeild

2/FL/LI
3/FL/VA
5/FL/LI

2 
SELECT ID, State, Group
FROM   [temp table] t
WHERE NOT EXISTS
(
    SELECT *
    FROM  [temp table] x
    WHERE x.ID = t.ID
    AND   x.state   = t.State
    AND   x.Group  = t.Group
    AND   x.Status = 'AC'
)
3

Hi khtan,

The query does not give the desired result as mentioned earlier. Any help to resolve is greatly appreciated.

4

Remove the "not" from khtan's where statement

5

Hi bitsmed,

The change you suggested also does not give the desired result. As I mentioned in my original post, I am expecting output as follows from the give temp table data as these combination of ID / State / Group does not have 'AC' status associated with it.

2/FL/LI
3/FL/VA
5/FL/LI

6

I don't see why @khtan s suggestion (modified as @bitsmed suggested) wouldn't work. Perhaps you are looking for one row per unique combination of Id, State and Group? If so,

SELECT
	ID,
	[State],
	[Group]
FROM
	YourTable
GROUP BY
	ID,
	[State],
	[Group]
HAVING
	SUM(CASE WHEN [Status]='AC' THEN 1 ELSE 0 END) = 0;
7

Hi James,

This is the query I was looking for. It is giving me desired results. Thanks for resolving the issue.