SQL Subqueries

Ciphon · 8 Jan 2009 at 12:45

Hi all,

I'm trying to run a subquery between 2 tables and I just can't get it to work

The 2 tables are below:

Users -- With columns of Name, Email, and StaffNumber
Users_Desc -- With columns of StaffNumber, Gender, and Job

My query is

Select Name
from Users
Where Job In (Select Job from Users_Desc Where Job = 'NULL')

I'm new to this and just have no idea what it's going wrong.

The error I'm getting is "Invalid column name 'Job'." which I assume is because it's looking in Users for it rather than User_Desc?

Any Ideas appreciated!

Ciphon · 8 Jan 2009 at 13:03

I literally created a load of random information just for the purposes of learning / testing ect..

Using -

SELECT Users.Staffnumber, Users.Name, Users_Desc.Job FROM Users
OUTER JOIN Users_Desc ON Users_Desc.StaffNumber=Users.StaffNumber
WHERE Users_Desc.Job IS NULL

Gave me the error Incorrect syntax near the keyword 'JOIN'.

Ciphon · 8 Jan 2009 at 13:09

Worked well with the left added on

Thanks very much... Like I say - I'm still pretty new to SQL / databases.. teaching myself and just created a few tables to do help me learn ect..

Ciphon · 8 Jan 2009 at 13:19

Just having a look at the results and it doesnt seem to have returned all of the results where Job = Null? Any idea's why that may be?

Ciphon · 8 Jan 2009 at 13:26

I changed

WHERE Users_Desc.Job IS NULL

to

WHERE Users_Desc.Job = 'NULL'

And it is now returning everything

Ciphon · 8 Jan 2009 at 13:37

I R Newb

Ciphon · 8 Jan 2009 at 15:23

How would I do the same quiery, with a subquiery - just for my information?

Ciphon · 8 Jan 2009 at 15:52

Pho said:
Heh, I take a break from work (currently writing import scripts to import hundreds of thousands of lines from a CSV file using SQL) to write yet more SQL .

I aspire to be you I suppose

That worked - thanks very much! I'll study it and see where I was going wrong!