I have the following table, with over 70K records:
test_1:
ClientID Category
22 Stress
22 Alcohol
22 Scizo
23 Stress
23 Alcohol
24 Stress
24 Scizo
25 Bi Polar
25 Cocaine
25 Meth
26 Stress
I need to SELECT only those ClientIDs, where Category = 'Stress', and also Category = 'Alcohol', within a ClientID.
So, I expect ClientIDs - 22, 23 in my output.
(ClientID 24 has only 'Stress' and no 'Alcohol'; same for ClientID 26, ClientID 25 has no 'Stress' no 'Alcohol'. Means 24, 25, 26 shouldn't be selected)
In this simple code my result includes ClientID = 22, 23, 24, 26. Where 'Stress' appears without 'Alcohol' in last 2 IDs.
SELECT
[ClientID]
,[Category]
FROM
[WH].[dbo].[Test_1]
WHERE
(0=0)
and (Category = 'Stress' or Category = 'Alcohol')
If I write my WHERE statement with AND
WHERE
(0=0)
and (Category = 'Stress' AND Category = 'Alcohol')
then I have no records displayed
Please HELP!
UPD - Question answered (see below)
Also, if I'd wanted to see the actual categories (not just IDs) in my query, then I do the following:
SELECT
m.[ClientID]
,m.[Category]
FROM
[WH].[dbo].[Test_1] m
INNER JOIN
(
SELECT
[ClientID]
FROM
[WH].[dbo].[Test_1]
WHERE
[Category] IN ('Stress', 'Alcohol')
GROUP BY
[ClientID]
HAVING COUNT(DISTINCT Category) = 2
) cte ON m.ClientID = cte.ClientID
I get the following result:
ClientID Category
22 Stress
22 Alcohol
22 Scizo
23 Stress
23 Alcohol
The problem with your current approach is that the WHERE clause is logic applied to a single record. Instead, you want to perform the category check across multiple records. One approach uses aggregation:
SELECT ClientID
FROM [WH].[dbo].[Test_1]
WHERE Category IN ('Stress', 'Alcohol')
GROUP BY ClientID
HAVING COUNT(DISTINCT Category) = 2;