SQL JOIN 3 TABLES WITH COUNT AND GROUP BY CLAUSE

Question

I Have 3 tables like that:

EXPEDITION (ID, CreateDate, Status);
PACKAGE (ID, EXPEDITION_ID)
ITEM (ID, EXPEDIITONPACKAGE_ID);

I need to know, for each expedition, the quantity of packages and the quantity of items.

Answer 1

UPDATE

This is the query that seems to have it.

    SELECT 
        E.ID, 
        P.Packages, 
        I.Items 
    FROM EXPEDITION E

    LEFT JOIN (
        SELECT DISTINCT E.ID, COUNT(P.ID) AS "Packages" FROM EXPEDITION E
        LEFT JOIN PACKAGE P
        ON E.ID = P.EXPEDITION_ID
        GROUP BY E.ID
    ) P
    ON E.ID = P.ID

    LEFT JOIN (
        SELECT DISTINCT P.ID as "PackageID", COUNT(I.ID) AS "Items" FROM PACKAGE P
        JOIN ITEM I
        ON P.ID = I.EXPEDIITONPACKAGE_ID
        GROUP BY P.ID
    ) I
    ON P.ID = I.PackageId

    GROUP BY 
        E.ID, 
        P.Packages, 
        I.Items

    ORDER BY 
        E.ID

It has two inner queries, that count the IDs separately, and they are joined in the main query to show the results.

Answer 2

Try this. Not tested yet...but it should work.. ;With c1 as ( Select e.expid, count(e.expid) as qtyPck From packages p inner join Expeditions e on p.expid = e.expid Group by e.expid ), C2 as ( Select i.pakId, count(i.pakId) as qtyItems From items i inner join packages p On i.pakId = p.pakId Group by i.pakid ) Select e.expId, p.qtyPck, I.qtyItems From expeditions e Join packages p on p.expId = e.expId Join items i on i.pakId = p.pakId;