MySQL LEFT JOIN with optional value in second table

Question

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

Answer 1

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.

Answer 2

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)

Answer 3

You can use LEFT JOIN

If its conditions match then values show otherwise null value shows.