I want to select all the rows from table1 that have keys that are not listed in a field in table2.

In it's simplist form:

Table 1 is a list of Books.
Table 2 is a list of Book IDs and Subject IDs

I want to select all the books that don't have a subject, the simplest way I can tell of doing this is to list all the Titles in table 1 who's keys are not mention in the book id column of table 2.

I am pretty sure the SQL here is fairly standard, just never come across this before.

If it helps I am using Oracle 8i (sorry not my choice).

TIA

Matt.

well assuming that Table 1 contains a title and an ID, then I would do a left join on the two tables as a starting point. now this looks a hell of a lot like coursework or something, so i'm not going to give you a complete solution... if it's not and you still need more assistance please clarify your situation.

lol course work? I work for a Library not done "course work" in a while. If it was coursework, then I'd have a clue as to what I was doing, because we would have been tought it!


I am working with a database that I am not allowed to change, I just have to run reports on it etc... and this one has me stumped.

There isn't anything more to clarify really... what do you want to know?

ok, i'll take your word for it then...

so as above it's a LEFT JOIN you want. which takes all the data in the left side table of the expression "SELECT * FROM TABLEA LEFT JOIN TABLEB" and matches it up with matching records in the right side table. if there is no matching data, i.e. no subject in your case, then the data is null for those fields. so the other part of the issue is to just select from the query rows where is description is null, e.g. "SELECT * FROM TABLEA LEFT JOIN TABLEB WHERE TABLEB.DESCRIPTION IS NULL"