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?
SELECT
Id,
SUM(Quantity) AS TotalAmount,
max(RealizationExists) as RealizationExists
from (
SELECT
Orders.Id,
OrderLines.Quantity,
CASE
WHEN EXISTS (SELECT * FROM OrderLineRealizations olr
where olr.OrderLineId = OrderLines.Id)
THEN 1
ELSE 0
END as RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
) tmp
group by id;
Here is DBFiddle demo