Databases
StackOverflow2013
Postsdbo
POHow to select data from multiple tables using joins/subquery properly? (PHP-MySQL)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to select data from multiple tables using joins/subquery properly? (PHP-MySQL)
    text
    copied!<p>I have three tables as shown in below image.</p> <p><strong><em>Note:</em></strong> <em>Lead column of projectheader table stores an employee id.</em></p> <p><img src="https://i.stack.imgur.com/9wjTR.png" alt="enter image description here"></p> <p>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)</p> <p>I was able to do that using the query below.</p> <pre><code>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 </code></pre> <p>And got this result: <img src="https://i.stack.imgur.com/Wd3S9.png" alt="enter image description here"></p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload