CODEWITHSUNDEEP
mysqlsqlgroup-by
Rajesh Omanakuttan
Rajesh Omanakuttan

Reputation: 6918

SQL Query Select based on a condition with Group By

I have a Batch entity and a Job entity. A Batch can have many Jobs.

Now, Job has a column called 'status' with values 'REVIEW', 'NEW', 'EDIT', and 'QA'.

I need to Select all Batches with a value 'YES' if any job belonging those batches is having the status 'REVIEW' and with 'NO' if no job is having the status 'REVIEW' and the query will be Grouped by Batch.

Select Batches.name, Batches.<column_I_need>, Batches.user_id
INNER JOIN Jobs ON Jobs.batch_id = Batches.id
GROUP BY Batches.id;

Upvotes: 3

Views: 7077

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would do this using case exists:

Select b.name, b.user_id,
       (case when exists (select 1
                          from jobs j
                          where j.batch_id = b.id and j.status = 'Review'
                         )
             then 'Yes' else 'No'
        end) as status
from batches b;

This approach is more efficient than an aggregation approach, because it eliminates the group by. With an index on jobs(batch_id, status), this would have optimal performance.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Use conditional aggregation to check your requirements:

SELECT
    b.id,
    b.name,
    b.user_id,
    CASE WHEN SUM(CASE WHEN j.status = 'REVIEW' THEN 1 ELSE 0 END) > 0
         THEN 'YES'
         ELSE 'NO' END AS status
FROM Batches b
INNER JOIN Jobs j
    ON j.batch_id = b.id
GROUP BY
    b.id;

Assuming that Batches.id be the primary key column of that table, then if we GROUP BY that column we may also select any column from the Batches table.

Upvotes: 3

Related Questions