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

I think you need to provide sample data & expected output to communicate what you're after. — OMG Ponies
– OMG Ponies, Commented Mar 12, 2011 at 19:48

Andomar · Accepted Answer · 2011-03-12 19:42:56Z

54

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.

answered Mar 12, 2011 at 19:42

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tstyle Over a year ago

thanks! So the key is to use ON and not WHERE. I was stuck for a while until I found this.

Louis Loudog Trottier Over a year ago

I had an impluse to go edit a dozen queries because of you.

cairnz · Accepted Answer · 2011-03-12 19:42:37Z

3

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)

answered Mar 12, 2011 at 19:42

cairnz

3,9571 gold badge21 silver badges23 bronze badges

Comments

Rana Hyder · Accepted Answer · 2015-05-24 10:43:15Z

1

You can use LEFT JOIN

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

answered May 24, 2015 at 10:43

Rana Hyder

851 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

MySQL LEFT JOIN with optional value in second table

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related