如何正确使用连接/子查询从多个表中选择数据？(PHP-MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11175901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select data from multiple tables using joins/subquery properly? (PHP-MySQL)
提问by Randel Ramirez
I have three tables as shown in below image.
我有三个表，如下图所示。
Note:Lead column of projectheader table stores an employee id.
注意：projectheader 表的 Lead 列存储员工 ID。
What I want to have is be able to retrieve something like the one in table my goal(Lead, displays the lead name of that employee)
我想要的是能够检索类似于我的目标表中的内容（领导，显示该员工的领导姓名）
I was able to do that using the query below.
我能够使用下面的查询做到这一点。
SELECT DISTINCT
projectdetails.ProjectDetailsID,
projectheader.ProjectID,
projectheader.ProjectName,
projectheader.Lead,
projectheader.StartDate,
projectheader.EndDate,
projectheader.Status,
projectheader.Remarks,
projectdetails.EmployeeID,
employee.Firstname,
employee.Lastname,
Lead.Leadname
FROM
projectheader,
projectdetails,
employee,
( SELECT
projectheader.ProjectID AS projid,
CONCAT(employee.Firstname,' ',employee.Lastname) AS Leadname
FROM employee, projectheader, projectdetails
WHERE projectheader.ProjectID = projectdetails.ProjectID
AND projectheader.Lead = employee.EmployeeID
) AS Lead
WHERE projectheader.ProjectID = projectdetails.ProjectID
AND projectdetails.EmployeeID = employee.EmployeeID
AND projectheader.ProjectID = Lead.projid
AND projectdetails.ProjectID = Lead.projid
And got this result:
并得到了这个结果：
The query that I used is quite long and perhaps not well written, I want to know a different way on how I could achieve the same result using a better sql query either by using join or a subquery. (I added a DISTINCT on the beginning of the projectdetails.ProjectDetailsID because without it some rows are duplicated). I'm in search for a better query than the one I'm currently using.
我使用的查询很长，可能写得不好，我想知道如何通过使用 join 或子查询使用更好的 sql 查询来获得相同的结果。（我在 projectdetails.ProjectDetailsID 的开头添加了一个 DISTINCT，因为没有它，一些行会重复）。我正在寻找比我目前使用的查询更好的查询。
采纳答案by The Nail
Try something like this (haven't tested it, you can give it a try):
尝试这样的事情（还没有测试过，你可以试一试）：
SELECT
projectdetails.ProjectDetailsID,
projectheader.ProjectID,
projectheader.ProjectName,
projectheader.Lead,
projectheader.StartDate,
projectheader.EndDate,
projectheader.Status,
projectheader.Remarks,
projectdetails.EmployeeID,
employee.Firstname,
employee.Lastname,
CONCAT(Lead.Firstname,' ',Lead.Lastname) AS Leadname
FROM
projectheader,
projectdetails,
employee,
employee as Lead
WHERE projectheader.ProjectID = projectdetails.ProjectID
AND projectdetails.EmployeeID = employee.EmployeeID
AND projectheader.Lead = Lead.EmployeeID
回答by Query Master
Try this Query i hope its work for you
试试这个查询我希望它对你有用
SELECT pd.ProjectDetailsID,ph.*,e.* FROM
`projectdetail` pd
INNER JOIN projectheader ph ON ph.ProjectID = pd.ProjectID
INNER JOIN employee e ON e.EmployeeID = pd.EmployeeID
