CODEWITHSUNDEEP
mysqlsqlwhere-clause
Robokop
Robokop

Reputation: 31

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

Upvotes: 2

Views: 55

Answers (5)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 1

JohanB
JohanB

Reputation: 376

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)

Upvotes: 1

Stu
Stu

Reputation: 32599

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;

Upvotes: 1

Sergey
Sergey

Reputation: 5217

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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;

Upvotes: 1

Related Questions