How can I join 2 tables even if some occurences are NULL MySQL (Left-join)-CodePudding

I currently have 2 tables, projects and data. Projects contains the list of all my projects and data contains information about what was done on the project, each day a row is added for each project on the data table.

A project can be created later on.

What I need to do is to get, on a specific day, all the data about all the projects and if a project was not created at the time, I need to know it and have NULL values for the data columns.

Here is what I tried :

SELECT project_key FROM projects p
LEFT JOIN (SELECT * FROM data d WHERE CAST(d.date_added as DATE) = '2022-11-08') as d
ON p.project_key = d.project_key

From what I understand, since I am doing a LEFT JOIN and since the subrequest is executed at first, my result should include the project that was not created at the time.

Here is a sample of the structure of my tables and what I want to have as a result :

projects

project_key     name
EV              Easier
CAN             Canva

data

id              time_spent      project_key         date_added
1               1000            EV                  2022-11-07
2               600             EV                  2022-11-08
3               1900            Canva               2022-11-09
4               2030            EV                  2022-11-09

expected result (for date_added = 2022-11-07)

project_key     time_spent
EV              1000
Canva           NULL

CodePudding user response：

If I read your question right:

You want to see all data rows on a given date?

SELECT p.project_key, d.project_key FROM projects p
RIGHT OUTER JOIN (SELECT * FROM data d WHERE CAST(d.date_added as DATE) = '2022-11-08') as d
ON p.project_key = d.project_key

This should show you the "optional" project key from the projects table in the first column, and the "required" project key from the data table in the second column

If this give you the opposite of your expected data set, just try to switch back to a Left outer join, should fix it :) And then I apologies for misunderstanding you