CODEWITHSUNDEEP
mysql
user2614806
user2614806

Reputation: 23

mySql - get common values from multiple queries

My database table looks like this:

enter image description here

I am trying to run a query that would return all the common user_id's where - for instance, field_id = 552 AND value = 'Savannah' AND field_id = 553 AND value = 'GA' AND field_id = 554 AND value = 'USA'. (so in this case, based on the records visible in my screenshot, the result would simply be '1').

I've been looking at joins and unions, but I feel I've gone down a rabbit hole and can't find my way back out.

Is this possible?

Upvotes: 0

Views: 199

Answers (1)

sgeddes
sgeddes

Reputation: 62861

Here's one option using conditional aggregation:

select user_id
from yourtable
group by user_id
having max(case when field_id = 552 then value end) = 'Savannah' and
       max(case when field_id = 553 then value end) = 'GA' and
       max(case when field_id = 554 then value end) = 'USA'

Upvotes: 1

Related Questions