How to select data from multiple tables using joins/subquery properly? (PHP-MySQL)
Solution 1
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
Solution 2
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
![Randel Ramirez](https://i.stack.imgur.com/D5gT3.png?s=256&g=1)
Randel Ramirez
{ C# | ASP.NET Core | Entity Framework Core | JavaScript | TypeScript | Web Applications | Mobile Applications | Xamarin | OOP } Get it to work. Make the code beautiful. Optimize.
Updated on August 16, 2020Comments
-
Randel Ramirez almost 4 years
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.