Retrieve Clients from Projects User Belongs to

Question

Users can have multiple projects and projects can have multiple clients. How do I get all the unique clients from the projects a user belongs to?

Tables: - Users - Projects - Clients - Project Clients

SELECT client_id, client_name FROM clients.. ? JOINS, USING.. ?.. what?

Answer 1

Firstly, if users can have multiple projects, you'll need a Project-Users table.

Given that, you can get what you want using either of these pieces of SQL:

select distinct c.id from
clients c
join projectclients pc on c.id=pc.clientid
join projects p on pc.projectid=p.id
join projectusers pu on p.id=pu.projectid
join users u on u.id=pu.userid
where u.id=3

select distinct c.id from
clients c join projectclients pc on c.id=pc.clientid
where pc.projectid in
(select projectid from
users u join projectusers pu on u.id=pu.userid
where u.id=3)