Home SQL QUERY – how to get child records with different types but from the same parent group

Questions

SQL QUERY – how to get child records with different types but from the same parent group

January 19, 2022

I needed a query where to return the finished type records from the service_flow_actions table, but the records must belong to the service_flow table group where there are records that were given play

Table work_order

id	company_id	closed	status
1	1	true	true
2	1	true	true

Table service_flow

id	work_order_id	company_id
1	1	1
2	2	1
3	2	1

Table service_flow_actions

id	work_order_id	service_flow_id	type	company_id
1	1	1	finished	1
2	1	2	play	1
3	2	2	finished	1
4	2	3	play	1
5	2	3	pause	1
6	2	3	finished	1

I had thought of something like that, but it didn’t work.

SELECT DISTINCT(service_flow_actions.*)
FROM service_flow_actions
INNER JOIN work_order ON service_flow_actions.work_order_id = work_order.id
LEFT JOIN service_flow_actions t ON work_order.id = t.work_order_id AND t.type = 'play' AND work_order.company_id = 37
WHERE work_order.company_id = 37 AND service_flow_actions.type = 'finished' AND work_order.closed = true AND work_order.status = true

>Solution :

The exists clause helps in such situation, of course you can achieve the same result using the join clause, but I would go for simple solution first, unless I face performance issues.

SELECT *
FROM service_flow_actions
WHERE type = 'finished'
AND EXISTS (
    SELECT 1
    FROM service_flow_actions sub
    WHERE sub.service_flow_id = service_flow_actions.service_flow_id
    AND sub.type = 'play' 
)