[SOLVED] How to display all from one table but only if they dont exsist in another

[esport]

Hi Guys,

Having troubles trying to produce a query. I want to display all users form the user table, but only display the users that are not assigned a project in the project table. How do I do this? I have tried using LEFT JOINS, but I think I may need to do inner queries.

 

user table

user_id

name

1

Dan

2

Joe

3

Ash

4

Ben

 

project table

user_id

project_id

1

2

3

2

 

So in this example the results should only display Joe and Ben

 

Thanks in advance for any help.

 

d

[Barand]

For this type of query you use a left join. Where there is no matching project, null values are returned in cols from that table

 

SELECT u.name

FROM user u

LEFT JOIN project p USING (user_id)

WHERE p.user_id IS NULL

 

see http://www.phpfreaks.com/tutorial/data-joins-unions

[esport]

Thanks for the quick response.

 

Im assuming where you have

 

USING (user_id)

 

the user_id is from the user table or the project table? Do I need to add a prefix?

 

I didnt seem to work. Also if I just want to check a certain project I just add in

 

WHERE p.project_id = $project_id

[Mark1inLA]

Or to take another approach (not sure what you guys think of sub-queries):

 

SELECT

  users.name

FROM

  users

WHERE

  users.user_id NOT IN

  (SELECT

    projects.user_id

  FROM

    projects)

 

[esport]

I will give it a go and get back to you, thanks heaps.

[esport]

Im not sure if this will make a difference but the coloumn name in the user table is 'id' not 'user_id' but 'id' is a reference in project table.

[esport]

this is what i have got so far using an inner query. It doesnt seem to work

 

SELECT e.name, e.surname, e.id as employee_id 
			  					  FROM users u, projects p
								  WHERE u.id=p.employee_id AND u.id NOT IN
								  (SELECT p.employee_id FROM prjects p WHERE p.project_id=$project_id)

[Barand]

Im not sure if this will make a difference but the coloumn name in the user table is 'id' not 'user_id' but 'id' is a reference in project table.

 

Yes, it makes a difference

 

SELECT u.name

FROM user u

LEFT JOIN project p ON u.id = p.user_id

WHERE p.user_id IS NULL

 

Joins are usually faster than subqueries

 

[esport]

Thanks im getting somewhere now. However when I add another clause it doesn't produce any results. This is what I am now using.

 

SELECT u.name, u.surname, u.id, p.project_id
FROM users u 
LEFT JOIN projects p ON u.id=p.user_id
WHERE p.project_id=$project_id AND p.user_id IS NULL

 

it doesn't like this

 

p.project_id=$project_id

 

thanks

[Barand]

this is covered in http://www.phpfreaks.com/tutorial/data-joins-unions

 

SELECT u.name, u.surname, u.id, p.project_id

FROM users u

LEFT JOIN projects p ON u.id=p.user_id AND p.project_id=$project_id

WHERE p.user_id IS NULL

[esport]

WOW THANKS HEAPS MATE.....  :D

It works great.