CODEWITHSUNDEEP
sqlpostgresql
Jaehyun Shin
Jaehyun Shin

Reputation: 1602

SQL query that match all list with group by

I have table that, 

-----------dates-------------
| id | date       | user_id |
| 1  | 2016-01-01 |    1    |
| 2  | 2016-01-02 |    1    |
| 3  | 2016-01-03 |    1    |
| 4  | 2016-01-04 |    1    |
| 5  | 2016-01-01 |    2    |
| 6  | 2016-01-02 |    2    |
| 7  | 2016-01-03 |    2    |
| 8  | 2016-01-01 |    3    |
| 9  | 2016-01-02 |    3    |

I want to make query that to get user_id match all date

like,

SELECT user_id FROM dates WHERE xxxxxx IN ('2015-01-01', '2015-01-02', '2015-01-03', '2015-01-04')
#=> result: 1

SELECT user_id FROM dates WHERE xxxxxx IN ('2015-01-01', '2015-01-02', '2015-01-03')
#=> result: 1, 2

SELECT user_id FROM dates WHERE xxxxxx IN ('2015-01-01', '2015-01-02')
#=> result: 1, 2, 3

Actually, my solution is using group by with count, but date can be duplicated. Any other solution?

I use postgresql

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use group by and having:

SELECT user_id
FROM dates
WHERE xxxxxx IN ('2015-01-01', '2015-01-02', '2015-01-03', '2015-01-04')
HAVING COUNT(DISTINCT xxxxxx) = 4;

Upvotes: 1

Related Questions