Simple SQL question

[Clarkey]

Bit of a mental block here, but basically I have 2 tables, I want a query to select all records from table A that are NOT also in table B. How is this done?
Thanks

 

[TrUz]

SELECT x, y, z FROM TableA WHERE x NOT IN (SELECT x FROM TableB);

TrUz

 

[Pho]

edit: beat

http://www.w3schools.com/sql/sql_join.asp
can't you do it with joins?

 

[Clarkey]

thanks truz I figured out your way, now its giving me this

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

for this

$sql = "SELECT * FROM courses WHERE level = '$_SESSION['user']->level'
AND courseID NOT IN (SELECT courseID from subscriptions
WHERE userID = '$_SESSION['user']->userID')";
$result = mysql_query($sql);

meh, i should go to bed :/


edit - fixed that, but the query gives no output, meh :/

edit - fixed

 

[happytechie]

you can do it with a sub query like above and it will will work and give you the correct results but be warned it is not the most efficient way of doing it is with a left outer join...

http://en.wikipedia.org/wiki/Left_outer_join#Left_outer_join

most modern DBMSs will optimise the query above in exactly the same way as a left outer join syntax anyway though so it probably doesn't matter too much.

Paul