[SOLVED] 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':

<?php $result1 = mysql_query("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

<?php && r_id='$r_id ?>

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

--brian

[bpgillett]

i put "&& r_id='$r_id" into the ON clause of the join instead of in the WHERE clause, and it works now.

 

--brian