Page 1 of 1

LEFT JOIN problem

Posted: Mon Apr 16, 2007 9:53 am
by 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':

Code: Select all

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

Code: Select all

&& r_id='$r_id
it returns zero results. any help would be great. thanks.
--brian

Posted: Mon Apr 16, 2007 11:21 am
by Begby
Give this a try, I haven't tested this but I think its close to what you want

Code: Select all

SELECT a.action
FROM competency_actions a
WHERE NOT EXISTS( 
  SELECT *
  FROM competency_logs l
  WHERE l.action_id = a.action_ID
  AND l.r_id={$r_id}
)
AND a.required = 1

Posted: Mon Apr 16, 2007 12:22 pm
by bpgillett
thanks for the suggestion. i ended up putting

Code: Select all

&& r_id='$r_id
into the ON clause of the join rather than the WHERE clause, and it works. thanks again.

--brian