Combine two resulting rows in a query

Question

I have the follow query:

select user_id from user_tags
where user_id = 3086533

which outputs:

Essentially, I want to be able to take this query, and change it to only include the user_id if both the name "master_sbx" and "phonePicked"appear.

So instead of writing a query of:

select user_id from user_tags
where user_id = 3086533

with an output of:

I would like to add the clause of only including user_id of only ids that have both "master_sbx" and "phonePicked" applied, so the output would just return

Hoping this makes sense. I'm not sure its actually possible in PostGreSQL but would love any feedback! It is piece of a larger query I am going to write.

Answer 1

If I understand correctly, you want test a user_id whether it (at least) appears in a row with name = 'master_sbx' as well as in a row with name = 'phonePicked'.

There are many ways to do this. The best choice depends on information that is not in your question. One way would be with EXISTS:

SELECT DISTINCT user_id
FROM   user_tags
WHERE  user_id = 3086533
AND    name = 'master_sbx'
AND    EXISTS (
   SELECT 1 FROM user_tags
   WHERE  user_id = 3086533
   AND    name = 'phonePicked'
   )

Another way with a self-join:

SELECT DISTINCT u1.user_id
FROM   user_tags u1
JOIN   user_tags u2 USING (user_id)
WHERE  u1.user_id = 3086533
AND    u1.name = 'master_sbx'
AND    u2.name = 'phonePicked';

DISTINCT is redundant if (user_id, name) is unique.

This can be treated as special case of relational division. We have assembled a whole arsenal of query techniques in this related answer:
How to filter SQL results in a has-many-through relation