-- 1. Create a small, controlled fixture.
DECLARE @Orders TABLE (
    OrderId int PRIMARY KEY,
    OrderValue decimal(12,2) NOT NULL
);
DECLARE @Lines TABLE (
    LineId int PRIMARY KEY,
    OrderId int NOT NULL,
    LineValue decimal(12,2) NOT NULL
);
DECLARE @Payments TABLE (
    PaymentId int PRIMARY KEY,
    OrderId int NOT NULL,
    PaidValue decimal(12,2) NOT NULL
);

INSERT INTO @Orders VALUES (101, 100), (102, 100), (103, 60);
INSERT INTO @Lines VALUES
    (1, 101, 60), (2, 101, 40), (3, 102, 100), (4, 103, 60);
INSERT INTO @Payments VALUES
    (1, 101, 50), (2, 101, 50), (3, 102, 100);

-- 2. Compare the source with the unsafe join.
SELECT SUM(OrderValue) AS SourceOrderValue
FROM @Orders;
-- Expected: 260.00

SELECT
    COUNT(*) AS JoinedRows,
    SUM(o.OrderValue) AS InflatedOrderValue,
    SUM(DISTINCT o.OrderValue) AS DistinctAmountTrap
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId;
-- Expected: 6 rows; 560.00; 160.00

-- 3. Locate orders that multiply in this joined result.
SELECT o.OrderId, COUNT(*) AS JoinedRows
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId
GROUP BY o.OrderId
HAVING COUNT(*) > 1;
-- Expected: OrderId 101, JoinedRows 4

-- 4. Aggregate each child independently, then join.
DECLARE @Report TABLE (
    OrderId int PRIMARY KEY,
    OrderValue decimal(12,2) NOT NULL,
    LineTotal decimal(38,2) NULL,
    PaidTotal decimal(38,2) NOT NULL
);

;WITH LineTotals AS (
    SELECT OrderId, SUM(LineValue) AS LineTotal
    FROM @Lines
    GROUP BY OrderId
),
PaymentTotals AS (
    SELECT OrderId, SUM(PaidValue) AS PaidTotal
    FROM @Payments
    GROUP BY OrderId
)
INSERT INTO @Report (OrderId, OrderValue, LineTotal, PaidTotal)
SELECT
    o.OrderId,
    o.OrderValue,
    l.LineTotal,
    COALESCE(p.PaidTotal, 0)
FROM @Orders AS o
LEFT JOIN LineTotals AS l ON l.OrderId = o.OrderId
LEFT JOIN PaymentTotals AS p ON p.OrderId = o.OrderId;

SELECT OrderId, OrderValue, LineTotal, PaidTotal
FROM @Report
ORDER BY OrderId;

SELECT
    COUNT(*) AS ReportOrders,
    SUM(OrderValue) AS CorrectOrderValue,
    SUM(LineTotal) AS CorrectLineValue,
    SUM(PaidTotal) AS CorrectPaidValue
FROM @Report;
-- Expected: 3 orders; 260.00; 260.00; 200.00

-- 5. Reconcile both coverage and values.
SELECT
    SUM(CASE WHEN LineTotal IS NULL THEN 1 ELSE 0 END)
        AS OrdersWithoutLines,
    SUM(CASE WHEN LineTotal IS NOT NULL
                  AND LineTotal <> OrderValue THEN 1 ELSE 0 END)
        AS OrdersWithLineMismatch
FROM @Report;
-- Expected: 0; 0

-- 6. If you only need a yes/no match, use EXISTS.
SELECT SUM(o.OrderValue) AS ValueOfOrdersWithPayments
FROM @Orders AS o
WHERE EXISTS (
    SELECT 1
    FROM @Payments AS p
    WHERE p.OrderId = o.OrderId
);
-- Expected: 200.00; this is order value, not cash received.