CODEWITHSUNDEEP
mysqlsql
Sinmok
Sinmok

Reputation: 656

MySQL select row only if multiple rows exist in group

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

Example database

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

Upvotes: 0

Views: 496

Answers (2)

Salvino D'sa
Salvino D'sa

Reputation: 4506

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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;

Upvotes: 2

Related Questions