如何正确使用联接/子查询从多个表中选择数据? (PHP-MySQL) [英] How to select data from multiple tables using joins/subquery properly? (PHP-MySQL)

问题描述

我有三个表，如下图所示.

I have three tables as shown in below image.

注意: projectheader表的领导"列存储员工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

得到以下结果:

我使用的查询很长，也许写得不好，我想知道一种不同的方式，关于如何通过使用join或subquery使用更好的sql查询来达到相同的结果. (我在projectdetails.ProjectDetailsID的开头添加了DISTINCT，因为如果没有它，某些行将被重复).我正在寻找比我目前正在使用的查询更好的查询.

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.

推荐答案

尝试类似的方法(尚未测试过，可以尝试一下):

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

这篇关于如何正确使用联接/子查询从多个表中选择数据? (PHP-MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！