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

Answers


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.


Need Your Help

Run as administrator: requireAdministrator & ClickOnce + emulating system time

c# winapi time clickonce administrator

My app uses ClickOnce tehcnology. Today I needed to run it as administrator. I modified the manifest file from

Deal with a drop_table migration for nonexistent table on heroku

ruby-on-rails postgresql activerecord rails-activerecord

I created a 'campaigns' table in my postgres db in my local env. I never committed the changes and thus never pushed that to production on heroku.