Left Join on association table

Question

I need help trying to join a table that uses an association table.

I have a users table

Users Table
user_id
user_name

Association Table
user_id
project_id

Project Table
project_id
project_name

I need to pull a user and the count of projects they are associated with.

SELECT u.user_name, COUNT(p.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
GROUP BY u.user_name

How do I associate the two tables?

Answer 1

If you want to associate projects and users, you need to do 2 joins:

SELECT u.user_name, COUNT(p.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
LEFT JOIN projects p ON p.project_id = a.project_id
GROUP BY u.user_name

If you want it faster you can do:

SELECT u.user_name, COUNT(a.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
GROUP BY u.user_name

Answer 2

I think you can do something like:

SELECT
    Utbl.user_name,
    NumTbl.numProjects
FROM
    UsersTable Utbl,
    (SELECT 
         Atbl.user_id, 
         COUNT(*) AS numProjects
     FROM 
         ProjectTable Ptbl,
         AssociationTable Atbl
     WHERE
         Utbl.user_id = Atbl.user_id AND
         Atbl.project_id = Ptbl.project_id) NumTbl
WHERE
    Utbl.user_id = NumTbl.user_id