SQL help on OUTER JOIN

[Prot]

Ok, all you SQL and MySQL experts. I have a question.
I need an SQL command to display only the results that return a NULL value. I know I need to use a LEFT JOIN command, but I'm getting all the results, NULLS included.

This is the command I have:

SELECT FLNAME, CLTERM, CLYEAR
FROM tblFaculty F LEFT JOIN tblCLasses C
ON F.FNUM=C.FNUM

FLName appears in tblFaculty and CLTERM and CLYEAR are in tblClasses.
I need to display results where F.Num has no corresponding value to C.FNum. Any Ideas? I'm missing something in my command.

thanks

[kschang]

Ok, all you SQL and MySQL experts. I have a question.
I need an SQL command to display only the results that return a NULL value. I know I need to use a LEFT JOIN command, but I'm getting all the results, NULLS included.

This is the command I have:

SELECT FLNAME, CLTERM, CLYEAR
FROM tblFaculty F LEFT JOIN tblCLasses C
ON F.FNUM=C.FNUM

FLName appears in tblFaculty and CLTERM and CLYEAR are in tblClasses.
I need to display results where F.Num has no corresponding value to C.FNum. Any Ideas? I'm missing something in my command.

thanks

How about a subquery?

SELECT FLNAME, CLTERM, CLYEAR
FROM tblFalculty F, tblClasses C
where F.FNUM NOT IN
(Select FNUM from tblClasses)

[tekkieman]

Check your PM.

Try:

SELECT F.FLNAME, C.CLTERM, C.CLYEAR
FROM tblFaculty F, tblCLasses C
WHERE F.FNUM=C.FNUM(+)

[Prot]

Check your PM.

Try:

SELECT F.FLNAME, C.CLTERM, C.CLYEAR
FROM tblFaculty F, tblCLasses C
WHERE F.FNUM=C.FNUM(+)

I tried that, tekkieman. I should say I have to do this SQL command in Access, and Access does not like the (+).

This query is going to be a 1 to none, and I did not take notes like I should have. Poor me

Well, the assignment is not due until Wednesday, and I will see another student in the class in one of my other classes. Guess I could ask him. Otherwise I will just have to ask instructor tomorrow.

Thanks for the replies.

[Prot]

I found the answer on my own. All I needed was an additional statement.

SELECT FLNAME, CLTERM, CLYEAR
FROM tblFaculty F LEFT JOIN tblCLasses C
ON F.FNUM=C.FNUM
WHERE CLTERM is NULL

[Supreme2k]

I would have suggested that, but for some reason I thought you had already put that in your original query. I could have sworn I saw that WHERE CLTERM is NULL line already.

[kschang]

SQL is at times quite counterintuitive, since it's kinda read "backwards".

[smearbrick1]

Dammit!!! The one question I could've answered and I miss it! Stupid knowing SQL!