How to get distinct count on sub table joined via two different way

Question

I have 3 below tables in my db

CREATE TABLE main_table
(
id   SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE middle_table
(
id            SERIAL PRIMARY KEY,
name          VARCHAR(255) UNIQUE NOT NULL,
main_table_id INT,
CONSTRAINT fk_main_table FOREIGN KEY (main_table_id)
    REFERENCES main_table (id)
);

CREATE TABLE sub_table
(
id              SERIAL PRIMARY KEY,
name            VARCHAR(255) UNIQUE NOT NULL,
main_table_id   INT,
middle_table_id INT,
CONSTRAINT fk_main_table FOREIGN KEY (main_table_id)
    REFERENCES main_table (id),
CONSTRAINT fk_middle_table FOREIGN KEY (middle_table_id)
    REFERENCES middle_table (id)
);

I have also inserted a sample data for convenience

INSERT INTO main_table (id, name) VALUES (1, 'Main 1');

INSERT INTO main_table (id, name) VALUES (2, 'Main 2');

INSERT INTO middle_table (id, name, main_table_id) VALUES (1, 'Middle 1', 1);

INSERT INTO sub_table (id, name, main_table_id, middle_table_id) VALUES (1, 'Sub 1', 1, 1);

INSERT INTO sub_table (id, name, main_table_id) VALUES (2, 'Sub 2', 2);

INSERT INTO sub_table (id, name, main_table_id) VALUES (3, 'Sub 3', 1);

and below is the query which I am running

SELECT mt.id,
    mt.name,
    count(DISTINCT st.id)             AS direct_st_count,
    count(DISTINCT mt_st.id)          AS via_middle_st_count
    --  count(DISTINCT mt_st.id OR st.id) AS st_count
    --  Please note I need distinct count of sub tables whether they are joined directly or via middle table
FROM main_table AS mt
    LEFT JOIN sub_table st ON mt.id = st.main_table_id
    LEFT JOIN middle_table ON mt.id = middle_table.main_table_id
    LEFT JOIN sub_table mt_st ON middle_table.id = mt_st.middle_table_id
GROUP BY mt.id;

I am unable to get distinct count of sub table(it can be joined directly or via middle table).

Answer 1

Check this:

SELECT mt.id main_id, mt.name main_name, st.id sub_id, st.name sub_name  
FROM main_table AS mt
LEFT JOIN sub_table st ON mt.id = st.main_table_id
WHERE st.middle_table_id IS NULL
-- AND st.main_table_id IS NOT NULL

UNION ALL

SELECT mt.id, mt.name, st.id, st.name
FROM main_table AS mt
LEFT JOIN middle_table ON mt.id = middle_table.main_table_id
LEFT JOIN sub_table st ON middle_table.id = st.middle_table_id
WHERE st.middle_table_id IS NOT NULL

ORDER BY main_name, sub_name

First subquery selects the rows joined directly and have no indirect joinings. Second selects indirect joinings.

fiddle

If you do not need in rows from main table which have no matched rows from sub table at all then uncomment the condition.