SQL Query Select based on a condition with Group By

Question

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;

Answer 1

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.