SQL QUERY - how to get child records with different types but from the same parent group
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
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'
)