SQL Subqueries

Ciphon

Ciphon

Ciphon

Associate
Joined
9 Dec 2008
Posts
2,341
Location
Somewhere!
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!
 
PHP: 
SELECT Users.Staffnumber, Users.Name, Users_Desc.Job FROM Users
LEFT OUTER JOIN Users_Desc ON Users_Desc.StaffNumber=Users.StaffNumber
WHERE Users_Desc.Job IS NULL

Maybe?
 
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 said:
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'.
Click to expand...

You're missing the LEFT that the guy above put in before OUTER JOIN.
 
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..
 
:)

Maybe the best thing to do would be to merge those two tables together as others have said, then create a new table for jobs where you can include job information (salary, description, etc) and do the same thing as above but for that :).
 
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?
 
I changed

WHERE Users_Desc.Job IS NULL

to

WHERE Users_Desc.Job = 'NULL'

And it is now returning everything
 
Try this (I think)

PHP: 
SELECT Users.Staffnumber, Users.Name FROM Users
WHERE Users.StaffNumber IN (SELECT StaffNumber FROM Users_Desc WHERE Job IS NULL OR Job = 'null')

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 :o.
 
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 :o.
Click to expand...

:eek: I aspire to be you I suppose :p That worked - thanks very much! I'll study it and see where I was going wrong!
 
It's not as fun as it sounds :p.

You were trying to use job rather than StaffNumber, so your query was something like:

Select name from users, where users.job (which doesn't exist) is contained in the array of jobs which are null; you were using nothing to link the two queries together (as job doesn't exist in both users and users_desc)

My version effectively does:
Select StaffNumber and Name from users, where StaffNumber is contained within the list of StaffNumbers from users_desc where job is null.
 
Sorry to hijack your thread mate, but i was wondering...when do you use cartesian join/product and when do you use naturaljoin/outerjoin?

Like the example given above by the OP, couldn't you merge the tables by using cartesian product? e.g Users x Users_desc
 
You must log in or register to reply here.
Back
Top Bottom