0

So I have this database schema

Project
    ID(PK)  NAME      Due_Date
    1       Alpha     1/1/2040
    2       Bravo     3/1/2030
    3       Charlie   2/1/2017
    4       Delta     4/1/2017

Employee
ID(PK)  NAME
1       Kevin
2       Mike
3       Eric
4       Ira
5       Peter

Project Assignment
ID(PK)  ProjectID(FK)   EmployeeID(FK)
1       1               1
2       1               2
3       2               2
4       2               3
5       3               3
6       3               4
7       1               3

Question -

I want to write a query that displays all projects and its Employees. If project has no employee it should still be included in the output.

I have written this query 

  select p.NAMe as project_Name,e.NAME as Employee_Name
from Project_Assignment pa inner join project p
  on pa.projectid = p.id
  inner join Employee e
  on pa.EmployeeID = e.ID

but this doesnt solve my problem. Because from the schema you can see project 4 is not in the project assignment table. However, I want project 4 to be displayed in my output as null

Sql_numskull
  • 5
  • 1
  • Definitely go check out that "Duplicate answer" link because understanding the different Joins is essential to wrangling your data. The quick answer is to change your INNER JOINs to LEFT OUTER JOIN and rerun. – JNevill Sep 14 '18 at 13:52
  • @JNevill - Thanks. I tried using left [outer] join before posting and it returned the same output? – Sql_numskull Sep 14 '18 at 14:03
  • In that case, start with the project table in your FROM clause (I suspect there are `projects` that aren't in `project_assignment` table? which isn't clear from your sample data). Like: `FROM project p LEFT OUTER JOIN project_assignment pa ON pa.projectid = p.id LEFT OUTER JOIN Employee e on pa.employeeid = e.id` That says "Take all records from `project` and only that match in `project_assignment` and then only those that match from `employee`". – JNevill Sep 14 '18 at 14:06
  • @JNevill. That makes more sense now. I was trying left outer join on the project_assignment which doesn't make sense. Thanks for your explanation! – Sql_numskull Sep 14 '18 at 14:19

0 Answers0