MySQL select row only if multiple rows exist in group-CodePudding

I am working with a database where multiple rows of data exist for the same related entity, in this case a user_id.

What I need to do is match user_ids with conditions on a per user basis rather than a per row basis..

For example, I need a list of users where they have rows with both:

meta_key = "utm_campaign" and meta_value = "Summer20222"

but also where they have

meta_key = "utm_medium" and meta_value = "qr_code"

for the same user_id.

Essentially, I'm trying to report How many users have the utm_campaign of "Summer2022" AND have the utm_medium of "qr_code" in this table

CodePudding user response：

Aggregate and assert both key-value requirements:

SELECT user_id
FROM yourTable
GROUP BY user_id
HAVING SUM(meta_key = 'utm_campaign' AND meta_value = 'Summer20222') > 0 AND
       SUM(meta_key = 'utm_medium' AND meta_value = 'qr_code') > 0;

CodePudding user response：

SELECT user_id, 
SUM(meta_key = 'utm_campaign' AND meta_value = 'Summer2022') as campaign, 
SUM(meta_key = 'utm_medium' AND meta_value = 'qr_code') as qr_code
FROM sample_data
GROUP BY user_id
HAVING campaign > 0 AND qr_code > 0

Live Demo