Select multiple values by category in T-SQL-CodePudding

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

CodePudding user response：

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;