sqlsql-server

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?


Solution

  • 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