Ollie Ollie - 6 months ago 11
SQL Question

Access Database - SQL, Filtering by Multiple Items in Same Column


I'm trying to create a SQL query in Access that will return all fields (SELECT *) when KEY is associated with three over the counter drugs (Motrin, Tylenol, and Bayer). In the example below, all rows for

AMYZ32874
would be returned and none of the rows for
WillyV32906
would be returned because he's only associated with 2 of the 3 drugs.

Starting Data:

Key Name DOB OTC
AmyZ32874 Amy Z 1/1/1990 Motrin
AmyZ32874 Amy Z 1/1/1990 Tylenol
AmyZ32874 Amy Z 1/1/1990 Bayer
WillyV32906 Willy V 2/2/1990 Motrin
WillyV32906 Willy V 2/2/1990 Tylenol
WillyV32906 Willy V 2/2/1990 Tylenol


Desired Result:

Key Name DOB OTC
AmyZ32874 Amy Z 1/1/1990 Motrin
AmyZ32874 Amy Z 1/1/1990 Tylenol
AmyZ32874 Amy Z 1/1/1990 Bayer


I'm pretty stuck.... This is my latest attempt.

SELECT *
FROM SAMPLEDB
WHERE OTC IN ('Tylenol', 'Motrin', 'Bayer') and Key in
(SELECT Key FROM SAMPLEDB
GROUP BY Key HAVING COUNT (Key) > 2);

Answer

It's a little ugly to look at, but I believe this should work:

SELECT 
  *
FROM 
  SAMPLEDB
WHERE 
  OTC IN ('Tylenol', 'Motrin', 'Bayer') 
  AND [Key] in
    (
      SELECT 
        [Key] 
      FROM 
        (
          SELECT 
            [Key]
            , OTC 
          FROM 
            SAMPLEDB
          GROUP BY 
            [Key]
            ,OTC
          WHERE 
            OTC IN ('Tylenol', 'Motrin', 'Bayer') 
        ) AS S1
      GROUP BY  
        [Key]
      HAVING 
        COUNT ([Key]) > 2
    );
Comments