SELECTing rows that do not appear in a different table?

[Aranell]

Hi, I was just wondering if anyone knew of a way to select rows from a database table that do not have the same ID as those in another table?

For example, I have two tables (Copies and Rentals), Copies contains basic information about the stock (copy number, rental title and format) whereas Rentals holds the copy number (along with some other, irrelevant, information) of the Copy(ies) currently being rented. (This is added to when the copy in question is rented out and then the row deleted when checked back in again.) What I need to do is to select all the copies from the Copies table that DO NOT appear in the Rentals (and have the additional functionality of being able to limit by format etc).

Here is the SQL that I tried but it simply selected all copies.copy_no's :S

SELECT copies.copy_no
FROM copies, rentals
WHERE copies.copy_no != rentals.copy_no
AND copies.rent_title = '$title'
AND copies.format = 'DVD'
GROUP BY copies.copy_no

(Btw, this is using MySQL 5.0)
Thanks in advance

[potterd64]

SELECT copies.copy_no from copies
where copies.copy_no NOT IN
(select rentals.copy_no from rentals)

Is a way to do it. This is oracle syntax so you'll probably have to change it a bit but thats the idea.