Deleting from a table using join / set operators.

I wonder if anyone can help me with the following SQL problem, as I am pretty rusty with it at the moment.

I have two tables

Table A
  ID char 4
  RANK char 1

Table B
  ID char 4
  REASON char 3

There is a one to many relationship between Table A and Table B, however there are records on table B which do not have an ID and table A, and I want to delete them.

If I do
SELECT ID from B MINUS SELECT ID from A

I get a list if those IDs which do not match, however I want to do the following ...

1. I want to display the ID and REASON for all the records in table B which do not have an ID in table A. How do I do this ? I presume its to do with putting a NULL or somesuch dummy field for A in the select, but can't work it out.
2. I want to then deleted them, but DELETE FROM B where (SELECT ID from B MINUS SELECT ID from A) does not work. Why is this, and what command should I issue?

Any help would be most appreciated ?

Thanks.

--
Dave Bevan
School Improvement Unit
Education Department
Cornwall County Council Received on Fri Mar 12 1999 - 07:16:33 CST