[SQL] How to check the existence of related records in group by query?

There are 3 simple tables Orders, OrderLines and OrderLineRealizations with an obvious relationships. I want to return some information about orders with an additional column that represents existence of a realization of any line for given group.

Simplified query looks like this:

SELECT
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
-- Is there a realization?
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id -- some additional grouping


I tried:

SELECT
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
CASE
WHEN EXISTS (SELECT * FROM OrderLineRealizations
WHERE OrderLineId IN (OrderLines.Id))
THEN 1
ELSE 0
END AS RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id


But it's not allowed to use IN like this. I also cannot use Left Join for realizations, because then I get an incorrect TotalAmount.

Result I want to achieve:

Orders
- - - -
Id
1
2

OrderLines
- - - -
Id | OrderId | Quantity
1 | 1 | 10
2 | 1 | 15
3 | 2 | 11

OrderLineRealizations
- - - -
Id | OrderLineId |
1 | 1 |

Result
- - - -
OrderId | TotalAmount | RealizationExists
1 | 25 | 1
2 | 11 | 0


What can I write a query to get an expected result?

Continue reading...