Three table join in sql

Question

I'm trying to join three tables.

Tables:

• HumanResources.Employees: Employee_ID(Primary Key), First_Name, Title -- also known as Employee_Title,
• ProjectDetails.TimeCards: Employee_ID(Foreignkey), Project_ID (Foreign Key)
• ProjectDetails.Projects: Project_Name, Project_ID(Primary Key)

I tried joining them using a temporary table.

select b.First_Name, b.Title, c.Project_ID from HumanResources.Employees b -- select statement
inner join ProjectDetails.TimeCards c on b.Employee_ID = c.Employee_ID -- first join seems to be the only one working.
inner join (select d.Project_Name as Project_Name, Project_ID from ProjectDetails.Projects d) as d on d.Project_ID=c.Project_ID -- second join doesn't seem to work.

Answer 1

I don't see the benefit of using a subquery in your query. Could you try the below query?

Make sure in your table Projects has matching Project_ID otherwise of course nothing would come up.

SELECT b.First_Name
    ,b.Title
    ,c.Project_ID
FROM HumanResources.Employees b
INNER JOIN ProjectDetails.TimeCards c ON b.Employee_ID = c.Employee_ID
INNER JOIN ProjectDetails.Projects d ON d.Project_ID = c.Project_ID

Answer 2

In the inner query for Project_ID use column alias name and then join on the alias column name.also try to have a different alias name for the sub query.

Answer 3

The use of the subquery is redundunt at best, and also the common alias of "d" may be a source of error.

Just do:

select b.First_Name, b.Title, c.Project_ID 
from HumanResources.Employees b 
inner join ProjectDetails.TimeCards c on b.Employee_ID = c.Employee_ID 
inner join ProjectDetails.Projects d on d.Project_ID=c.Project_ID