Inner join with Count, multiple tables

I’m having an issue with MS Access to get correct counts on multiple tables. I’m not at all an expert in SQL requests.

I have a set of tables which is structured as follow :

• Table1 : UID, Name, other informations
• Table2 : UID, FK_UID_Table1, Name, other informations
• Table3 : UID, FK_UID_Table2, Name, other informations
• Table4 : UID, FK_UID_Table3, Name, other informations

Each FK reference is 1-N, with N >= 0. So it is possible that an item in Table2 has no reference in Table3 FK.

What I’m trying to achieve is to get the count of each items in one query.

For example, if I select an item in Table1 I would like to get :

Table1.Name | Count(Table2.UID) | Count(Table3.UID) | Count(Table4.UID)

I succeed to get the exact count with one INNER JOIN as follow :

SELECT g.UID, Table1.Name, COUNT(s.UID) AS CountTable2
FROM Table1 AS g INNER JOIN Table2 AS s ON s.FK_Table1 = g.UID
GROUP BY g.UID, g.Name

For example, with an item in Table1 who gets 2 references in Table2, and 3 references in Table3, I get 2 as a result, which is correct.

When I try to add an other layer of counting, I did as follow :

SELECT g.UID, g.Name, COUNT(s.UID) AS CountTable2, COUNT(p.UID) AS CountTable3
FROM (Table1 as g
INNER JOIN Table2 AS s ON s.FK_Table1 = g.UID)
INNER JOIN Table3 AS p ON p.FK_Table2 = s.UID
GROUP BY g.UID, g.Name, CountTable2, CountTable3

With my previous example, instead of getting 2 for CountTable2 and 3 for CountTable3 as result, I get 3 and 3.

I read that this is an expected behaviour with Access, but I didn’t find how to make it works.

I was helped by this question : Inner join with count on three tables

Any help would be grately appreciated.

Answer

As I don’t succeed to get a result with Gordon Linoff answer, I tried an other way with LEFT OUTER JOIN.

Thanks to this post : How to get a group where the count is zero

I managed to get a correct result with void relations (for example, an item Table2 has no reference in Table3), but now I got an incorrect result when I join more than 2 tables.

I guess it’s a problem from my request…

Currently :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID
GROUP BY Table1.UID, Table1.Name

gives me a correct result (only two tables related), but :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2, COUNT(Table3.UID) AS CountTable3
FROM (Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID)
LEFT OUTER JOIN Table3 ON Table3.FK_Table2 = Table2.UID
GROUP BY Table1.UID, Table1.Name

gives me an incorrect result for CountTable2, which appears to be more than expected. CountTable3 is correct.

EDIT :

I finally figure how to make it works, according to my research and the hint from Gordon Linoff with aggregation before joining.

I start by counting the deepest table inside the table above, then join, and so on. At every step, I select the essentials informations to keep : UID, FK_AboveTable, Count, Sums from deepest table.

Final code :

SELECT Table1.UID, Table1.Name, COUNT(Table2.UID) AS TotalTable2, SUM(CountTable3) AS TotalTable3, SUM(CountTable4_2) AS TotalTable4
FROM Table1 LEFT OUTER JOIN (
    SELECT Table2.UID, Table2.FK_Table1, COUNT(Table3.UID) AS CountTable3, 
    SUM(CountTable4) AS CountTable4_2
    FROM Table2 LEFT OUTER JOIN (
        SELECT Table3.UID, Table3.FK_Table1, COUNT(Table4.UID) AS CountTable4
        FROM Port LEFT OUTER JOIN
            Table4 ON Table4.FK_Table3 = Table3.UID
            GROUP BY Table3.UID, Table3.FK_Table2
        ) Table3 ON Table3.FK_Table2 = Table2.UID
        GROUP BY Table2.UID, Table2.FK_Table1
    ) Table2 ON Table2.FK_Table1= Table1.UID
GROUP BY Table1.UID, Table1.Name ORDER BY Table1.Name DESC

Note that void count from deepest table appears are void and no 0 (for example, if there is one item in Table1, none related in Table2, the count will be 0 for Table2, void for Table3 and Table4).

I assume this might be upgraded, but for now it solves my issue, and allows me to add as many tables as I need.