MySQL LEFT JOIN with optional value in second table
I have two tables: projects and user_licenses.
I'd like to grab the entire list of projects from the database, as well as the user's current license state. The licenses table has a user ID field which I check against a $_SESSION variable for the value. The thing is, they might not have a license, or a non-logged in visitor may want to see the projects list. My question is this: How can I get the data from the left table always display, but only grab data for that row from the right table when certain conditions are met?
The query I have at the moment is this:
SELECT Projects.*, UserLicenses.* FROM Projects LEFT JOIN UserLicenses ON Projects.id = UserLicenses.project_id WHERE UserLicenses.user_id = 12 ORDER BY name ASC
Add any extra conditions to the on clause of the left join. They will only affect the joined table.
SELECT Projects.*, UserLicenses.* FROM Projects LEFT JOIN UserLicenses ON Projects.id = UserLicenses.project_id and UserLicenses.user_id = 12 and UserLicences.Question = '6*7' and UserLicences.Answer = 42 ORDER BY name ASC
This will return projects without matching licenses.
Move the UserLicenses condition away from WHERE, and up to the JOIN condition. By having it in the WHERE part, you will never see those "left" rows because they are filtered away.
You can also probably use WHERE (UserLicenses.user_id = 12 OR UserLicenses.user_id IS NULL) Don't do that. Just move it to the join condition like this:
LEFT JOIN UserLicenses ON (Projects.id = UserLicenses.project_id AND UserLicenses.user_id = 12)
You can use LEFT JOIN
If its conditions match then values show otherwise null value shows.