[SOLVED] question on table joins

[mattennant]

Hi there

 

I'm trying to display the results of a user table (userdetails) who have not been assigned a particular task in a task table (display_tasks) and to be honest i'm struggling a bit.

 

i can select the users from  the task table who have been assigned the task like this

a)

SELECT

FROM userdetails LEFT JOIN display_tasks

ON display_tasks.user_id = userdetails.number

WHERE display_tasks.task_ref = '170'    //170 is the task reference i will get this in from the url

 

but if i try and select the opposite i get all the users who have been assigned other tasks

b)

SELECT

FROM userdetails LEFT JOIN display_tasks

ON display_tasks.user_id = userdetails.number

WHERE display_tasks.task_ref != '170'  //170 is the task reference i will get this in from the url

 

Is there a simple way to do this, put  simply terms i want to select all the users from the user table unless they have already been assigned that particular task.

 

Thanks

[mezise]

Hi,

 

try this:

SELECT userdetails.number
FROM userdetails
LEFT JOIN display_tasks ON display_tasks.user_id = userdetails.number
  AND display_tasks.task_ref = '170'
WHERE
  display_tasks.user_id IS NULL
;

[mattennant]

that worked a treat, thanks so much