Select customer if two conditions are met across multiple rows-CodePudding

I want to output customers who meet criteria in multiple columns across multiple rows.

Say this is my data:

id	type	event
1	hot	inside
1	cold	gym
1	medium	outside
2	cold	inside
2	cold	outside
2	hot	inside
3	hot	gym
3	cold	inside
3	medium	outside

I want to keep customers who have type "medium" and event "gym." However, as you can see no customer has those in the same row. I want to keep only the customers and the rows that meet those criteria. So I want the results to look like:

id	type	event
1	cold	gym
1	medium	outside
3	hot	gym
3	medium	outside

So it should drop the rows from each customer that do not meet one of the two criteria and drop all customers who do not meet both criteria.

CodePudding user response：

One solution is to count the rows for every id and the criteria

CREATE TABLE mytable (
  "id" INTEGER,
  "type" VARCHAR(6),
  "event" VARCHAR(7)
);

INSERT INTO mytable
  ("id", "type", "event")
VALUES
  ('1', 'hot', 'inside'),
  ('1', 'cold', 'gym'),
  ('1', 'medium', 'outside'),
  ('2', 'cold', 'inside'),
  ('2', 'cold', 'outside'),
  ('2', 'hot', 'inside'),
  ('3', 'hot', 'gym'),
  ('3', 'cold', 'inside'),
  ('3', 'medium', 'outside');

SELECT * FROM mytable m1 
WHERE ("type" = 'medium' OR "event" =  'gym') 
and ((Select COUNT(*) FROM mytable WHERE "id" = m1."id" AND ("type" = 'medium' OR "event" =  'gym')  GROUP BY "id") = 2
OR (Select COUNT(*) FROM mytable WHERE "id" = m1."id" AND ("type" = 'medium' AND "event" =  'gym')  GROUP BY "id") = 1)

id | type   | event  
-: | :----- | :------
 1 | cold   | gym    
 1 | medium | outside
 3 | hot    | gym    
 3 | medium | outside

db<>fiddle here

CodePudding user response：

You can solve this by creating two result sets, one that is customers that meet your first criteria, and another with customers that fit your second criteria, and then inner joining:

SELECT medium_customer.id
FROM (SELECT id FROM yourtable WHERE type = 'medium') medium_customer
    INNER JOIN (SELECT id FROM yourtable WHERE event='gym') gym_customer
    ON medium_customer.id = gym_customer.id

You could also use the INTERSECT operator to keep only records where two result sets match:

SELECT DISTINCT id FROM yourtable WHERE type='medium'
INTERSECT
SELECT DISTINCT id FROM yourtable WHERE event='gym';

Another option is a correlated subquery:

SELECT id
FROM yourtable as yt
WHERE type='medium'
   AND EXISTS 
       ( 
          SELECT 1
          FROM yourtable
          WHERE yt.id = id
             AND event='gym'
       )

Lastly, you could aggregate and use a HAVING clause to keep records that fit the criteria:

SELECT id
FROM yourtable
WHERE type='medium' OR event='gym'
HAVING COUNT(*) >= 2

Note that this will fail if a customer has more than one entry for type='medium' or event='gym'. In other words it's dependent on your data's uniqueness, so only use it if you are certain it won't generate false positives.