How to compare the values of one column? SQL(Postgres)

Question

Postgres

I have table user_answers:

----------------------------
| id | user_id | answer_id |
----------------------------
|  1 |    47   |   121     |
----------------------------
|  2 |    47   |   125     |
----------------------------
|  3 |    47   |   141     |
----------------------------
|  4 |    49   |   122     |
----------------------------
|  5 |    49   |   121     |
----------------------------
|  6 |    49   |   101     |
----------------------------
|  7 |    52   |   121     |
----------------------------
|  8 |    52   |   125     |
----------------------------
|  9 |    52   |   101     |
----------------------------
| 10 |    67   |   101     |
----------------------------

I would like to get user_id, only user_id where answer_id = 121 and answer_id = 125.

A good result: 47 and 52 because: user_id = 49 has 121, but no 125

This simple query does not work (returns nothing):

SELECT user_id FROM user_answers
WHERE answer_id = 121 AND answer_id = 125

princelySid · Accepted Answer · 2022-09-28 13:02:13Z

3

Your query returns nothing because answer_id has only one value on any given row. It cannot have multiple values on the row.

This is an example of a set-within-sets query. I would recommend using group by and having. Here is one method:

SELECT user_id
FROM user_answers
WHERE answer_id IN (121, 125)
GROUP BY user_id
HAVING COUNT(DISTINCT answer_id) = 2;

THis will return values that have 121, 125 and other values. To get only those two values:

SELECT user_id
FROM user_answers
WHERE answer_id IN (121, 125)
GROUP BY user_id
HAVING SUM(CASE wHEN answer_id = 121 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE wHEN answer_id = 125 THEN 1 ELSE 0 END) > 0;

edited Sep 28, 2022 at 13:02

princelySid

7,0472 gold badges18 silver badges18 bronze badges

answered Jan 16, 2015 at 12:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

We should have a tag for this kind of problems. They come up on regular basis

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . More power to you. I call them "set-within-sets". I don't know if there is another name.

Clodoaldo Neto · Accepted Answer · 2015-01-16 12:34:03Z

2

select user_id
from user_answers
where answer_id in (121, 125)
group by user_id
having count(*) = 2

answered Jan 16, 2015 at 12:34

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

goodevans · Accepted Answer · 2015-01-16 12:36:13Z

0

You need Or, not And.

SELECT user_id FROM user_answers WHERE (answer_id = 121 OR answer_id = 125)

Your original query is looking for results where answer_id is both 121 and 125 at the same time. Using OR looks for results where answer_id is either value.

answered Jan 16, 2015 at 12:36

goodevans

1365 bronze badges

1 Comment

Michael Ekoka Over a year ago

For anyone wondering why this is not a complete solution in comparison to the accepted one. This will return any user_id where answer_id is 121 or 125. So it will return user_id 49, which was specifically indicated in the question as a bad result because that user does not have both answers. To fix, a group by clause is necessary on the user_id. The group by then allows to tally up with count(*) how many rows the user_id is present in. All that's left now is to filter out with a having clause any user_id that is counted less than twice (once for each 121 and 125).

Collectives™ on Stack Overflow

How to compare the values of one column? SQL(Postgres)

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related