java threetable,Three table join in sql

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

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.

回答1:

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

回答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.

回答3:

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