LEFT JOIN problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bpgillett
Forum Newbie
Posts: 11
Joined: Mon Jul 26, 2004 3:25 am

LEFT JOIN problem

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
bpgillett
Forum Newbie
Posts: 11
Joined: Mon Jul 26, 2004 3:25 am

Post 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
Post Reply