CODEWITHSUNDEEP
sqlmany-to-many
Martynas
Martynas

Reputation: 188

SQL how to select all rows from table joining other table even if there are no rows associated

I have 3 tables

Projects   Persons      PeopleInProjects
 - Id      - Id         - Id
 - Name    - Name       - PersonId
           - Role       - ProjectId

How would I select all Project names and Persons associated with that project where Persons Role is 'admin'?

PeopleInProjects is just a many to many relationship tables with Ids of all people associated with projects. Projects can have many or none Persons associated with them.

I need to select all projects only once and I need to select projects even if they don't have a Person with Role 'admin' associated with them.

Select pr.Name, p.Name 
from Projects as pr 
join PeopleInProjects as pp on pr.Id = pp.ProjectId
join Persons as p on pp.PersonId = p.Id
Where p.Role = 'admin'

Only selects the Projects which have an admin associated with them and not all projects

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

If you want all projects and then any admins that might happen to be assigned, use left join:

Select pr.Name, p.Name 
from Projects pr left join
     (PeopleInProjects pp join
      Persons p
      on pp.PersonId = p.Id and p.Role = 'admin' 
     )
     on pr.Id = pp.ProjectId ;

Upvotes: 1

Related Questions