SQL Select from multiple tables and count third

I want to count number of rows in table that is referenced by two fields of other tables.

I’ve the following structure

branches table
id, name
1, dev
2, master

levels table
id, name
1, easy
2, hard

repos table
id, name, branch_id, level_id
1, repo-1, 1, 1
2, repo-2, 2, 2

Result that I want to achieve

b_id, l_id, r_cnt
1, 1, 1
1, 2, 0
2, 1, 0
2, 2, 1

The statement that I’ve tried, but getting error at right join

SELECT
    b.id,
    l.id,
    COUNT(r)
FROM
    branches b,
    levels l
    RIGHT OUTER JOIN repos r ON r.branch_id = b.id
    AND r.level_id = l.id;

What I’m missing?

Answer

You are quite close. Main problems with your query:

• you are mixing implicit and explicit joins: just don’t. Explicit joins are evaluated first, causing the error that you are getting (implicit joined tables are not yet identified)

• you want a right join, not a left join; right join are quite counter-intuitive, I would recommend avoiding them in general

Consider:

select b.id b_id, l.id l_id, count(r.id)
from branches b
cross join levels l
left join repos r on r.branch_id = b.id and r.level_id = l.id
group by b.id, l.id

This works by generating all possible combinations of branches and levels using a cross join (that’s basically a cartesian product of both tables), and then bringing the repos table with a left join. The rest is aggregation and counting how many repos records match each branch/level combination.