How to make an SQL query where in WHERE there will be 2 times "AND"

user_17924501 0
Viewed 48

Please help me deal with a seemingly simple sql query, but which does not work, I have already tried all the options through JOIN and through variations in WHERE

Таблица User

id status postId
12345 new 55555
23456 ready 55555
34567 done 77777

I need to output postId if it has status = new And status = ready. postId = 55555 should be output My option:

SELECT
       postId
FROM User
WHERE postId IN (55555, 77777)
AND (status = new AND status = ready)
GROUP BY postId

But it does not work because of a contradiction (status = new AND status = ready), it only works (status = new OR status = ready), but such a condition does not suit me, it needs to be strictly both new and ready. MySql.

I also have SQL with nested request:

SELECT postId FROM User WHERE status = ready AND postId IN 
    (SELECT postId FROM User WHERE status = new )

It works, but if I need process more than 2 status the request becomes more complicated

where-clause mysql sql
5 Answers

To make your first approach work, you need to assert the status requirements in the HAVING clause:

SELECT postId
FROM User
WHERE postId IN (55555, 77777)
GROUP BY postId
HAVING SUM(status = 'new') > 0 AND SUM(status = 'ready') > 0;
user_18632290 
SELECT U.POSTID
FROM USER AS U
WHERE U.STATUS='NEW' 
AND EXISTS
(
  SELECT 1 FROM USER AS U2 WHERE U.POSTID=U2.POSTID AND U2.STATUS='READY'
)
user_145355170
user_164619520

You can use aggregation with distinct:

select postId
from T
where postId in (55555,77777)
  and status in ('new','ready')
group by postId
having Count(distinct status) = 2;
user_153326500

Can you try

SELECT
       postId
FROM User u
WHERE postId IN (55555, 77777)
AND status = new AND EXISTS(
    select postId 
    from User u2 
    WHERE postId IN (55555, 77777) 
    and status = ready 
    and u2.postID = u.postId)
user_127036380
Related