SQL: where condition with all

Question

user table

id : Integer
name: String

user_review table

user_id: Integer
review: Integer

Now, the user record is having many user_review records.

I need to get all users who has 5 review and 4 review.

SELECT * FROM user JOIN user_review ON user.id = user_review.user_id
 WHERE user_review.review = 5 AND user_review.review = 4

Abobe code is retuning empty result.

I tried below code, but, return wrong result

SELECT * FROM user JOIN user_review ON user.id = user_review.user_id
 WHERE user_review.review in [5, 4]

Sample data Users

id name
1  MM
2  EE

user_profile

user_id, review
1        4
1        5
2        4
2        5
3        4
4        5

Wanted result

id name
1  MM             // user1, having (review4) and (review5)
2  EE             // user2, having (review4) and (review5)

please provide sample data and show us what you get and what you expect? — eshirvana, Apr 07 '21 at 18:44
The second query is what I would have suggested based on your question. What results did you get and how did they differ from what you want? — Nicholas Hunter, Apr 07 '21 at 18:45
select U.id, U.name from user U inner join user_review R on U.id=R.user_id where review in (4,5) group by (U.id); — Daniele Rugginenti, Apr 07 '21 at 19:08

score 2 · Answer 1 · answered Apr 07 '21 at 18:49

2

I think this is what you want. All users who have both a review = 4 and a review = 5

select * from users 
where exists(select * from user_review where user_id = user.id and review = 4)
and exists(select * from user_review where user_id = user.id and review = 5)

answered Apr 07 '21 at 18:49

Nicholas Hunter

1,791
1
11
14

1

Yes, this is right, but, the query seems complex – Wang Liang Apr 07 '21 at 18:50
That's some code that will stay in your codebase and not change anymore probably, 3 lines of code for a query is not what I would call "complex". @MERN do you want these values (4 and 5) to be somehow dynamic? – Pac0 Apr 07 '21 at 18:52
I guess complexity is in the eye of the beholder. – Nicholas Hunter Apr 07 '21 at 18:55

SQL: where condition with all

1 Answers1