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

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

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)

CodePudding user response：

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