CODEWITHSUNDEEP
mysqlsql
JK2018
JK2018

Reputation: 489

SQL select with double conditions

I need to select the parent_ids that have both status = pending & processing, or the parent_ids who have both status = canceled and processing. grouped by parent_id.

I only manage to get parent_ids that have either pending, processing, or canceled as status which is not what I need. I need the double conditions.

I've tried something similar to:

select parent_id, status from tableName
where status = ('processing' and 'pending')
or status = ('processing' and 'canceled') group by parent_id

Image

Upvotes: 0

Views: 119

Answers (1)

Akina
Akina

Reputation: 42632

SELECT parent_id
FROM tableName 
GROUP BY parent_id
HAVING SUM(status = 'processing')
   AND SUM(status IN ('pending', 'cancelling'));

The query checks does for particular parent_id a row (at least one) with 'processing' and a row with 'pending'/'cancelling' (any of) exists.

Alternatively you may use

SELECT DISTINCT parent_id
FROM tableName t1
JOIN tableName t2 USING (parent_id)
WHERE t1.status = 'processing'
  AND t2.status IN ('pending', 'cancelling');

This query do the same but in another form.

One more alternative:

SELECT DISTINCT parent_id
FROM tableName t1
WHERE status = 'processing'
  AND EXISTS ( SELECT NULL
               FROM tableName t2
               WHERE t1.parent_id = t2.parent_id
                 AND t2.status IN ('pending', 'cancelling') );

Upvotes: 1

Related Questions