How to select data from multiple tables using joins/subquery properly? (PHP-MySQL)

join, mysql, php, subquery

Solution

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

Problem

I have three tables as shown in below image. Note: Lead column of projectheader table stores an employee 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.

Original source