php - Missing data from joined table on query
I am trying to get information from a big query using 2 main tables (projects and test_cases), the problem is that this query only returns the information when the primary key matches on both tables. Meaning that if I have registers on "projects" but none matches on test_cases all the "left" table information is not displayed.
I am expecting normal values on a typical "left join" clause (If values does not match, every other field is filled with "null" information.
This is the structure from both tables: Projects: ID, Project_Name, N_TestCases, Start_Date, PTF, Status. Test_Cases: ID, IDProject, TC_Description, Remarks, Status, Latest_Update.
I had two projects registered on the table "Projects" and just one on "Test_Cases".
| ID | Project_Name | N_TestCases | Start_Date | PTF | Status |
| -------- |Answer
Answer
Answer
Answer
Answer
Answer
Answer
Answer
Answer
Answer
Answer
Answer
| ID | IDProject | TC_Description | Remarks | Status | Latest_Update |
| -------- | --------- |Answer
Answer
Answer
Answer
This is the query I made:
SELECT
projects.ID, projects.Project_Name, projects.N_TestCases,
(SUM(case when test_cases.Status = 'PASS' then 1 else 0 end)+SUM(case when test_cases.Status = 'PASS' then 1 else 0 end)) as 'Complete',
SUM(case when test_cases.Status = 'PASS' then 1 else 0 end) as 'PASS',
SUM(case when test_cases.Status = 'FAIL' then 1 else 0 end) as 'FAIL',
SUM(case when test_cases.Status = 'Blocked' then 1 else 0 end) as 'Blocked',
SUM(case when test_cases.Status = 'WNE' then 1 else 0 end) as 'WNE',
SUM(case when test_cases.Status = 'Running' then 1 else 0 end) as 'Running',
(projects.N_TestCases-SUM(case when test_cases.Status = 'PASS' then 1 else 0 end)-SUM(case when test_cases.Status = 'FAIL' then 1 else 0 end)-SUM(case when test_cases.Status = 'Running' then 1 else 0 end)) as 'Pending',
projects.Start_Date,projects.PTF,projects.Status
FROM projects INNER JOIN test_cases ON projects.ID = test_cases.IDProject;
This query works as long as I have a second register on "Test_Cases" using the main relation ID (Projects.ID/Test_Cases.IDProject). Otherwise, I only get the information for a single project. (Refer to attached image)Single Project
I had also tried LEFT-JOIN, RIGHT-JOIN, INNER-JOIN, CROSS-JOIN but none of this worked. One of the things I had also discovered is that if I remove very operation and use a single query like "SELECT * from Projects LEFT JOIN Test_Cases ON Projects.ID = Test_Cases.IDProject;", I get the results I want.
Source