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

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

mysqlsqlwhere-clause


Solution 1:[1]

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;

Solution 2:[2]

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'
)

Solution 3:[3]

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;

Solution 4:[4]

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)

Solution 5:[5]

I need to output postId if it has status = new And status = ready

An easy solution:

select postId
from User
where status in ('new','ready')
group by postId
having count(distinct status) =2;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=da123e47eef3998f4d984453ca96b790

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Sergey
Solution 3 Stu
Solution 4 JohanB
Solution 5

Related Questions