[RESOLVED] LEFT JOIN problem

[bpgillett]

hi. i have two tables . one table, 'competency_actions', has the unique id's for different actions ('action_id') and the action names ('action'). the other table, 'competency_log', has fields about these actions and the unique id's for the students ('r_id') who completed them. some actions are required and have the field, 'required', set to 1 in the 'competency_actions' table. i am trying to select the actions of any required actions from table 'competency_actions' that are not present in table 'competency_log' for a given student ('r_id'). i can't figure out how to specify the particular student that i'm performing the query for as 'r_id' will be null in my LEFT JOIN. here's what i have so far with out specifying the 'r_id':

SELECT competency_log.action_id, competency_actions.action_id, competency_actions.action FROM competency_actions LEFT JOIN competency_log on competency_actions.action_id = competency_log.action_id WHERE required = '1'  && competency_log.action_id IS NULL

this works, but if i include

&& r_id='$r_id

it returns zero results. any help would be great. thanks.
--brian

[tomhath]

Put && r_id='$r_id into the ON clause of the join, not in the WHERE clause.

[bpgillett]

thanks! works perfectly.

--brian

[bradgrafelman]

Don't forget to mark this thread resolved.