Hi I am sure this is a simple question - I have two tables which contain a number of rows of the same information. I need to be able to create a table which excludes the records that appear in both tables.
Shell (BOB member since 2007-04-18)
This should probably be posted in the Job Design sub-forum.
I’m going to assume that you have an ID field that is common to both tables.
I’d suggest getting a query that joins table A and table B with an inner join on the ID, to return the ID of the records that match in both tables. (call this C).
Then get a different query that combines the A + B data together (if they are identical perform a merge then a query with DISTINCT, otherwise perform an outer join on ID and use nvl(A.value, B.value) to get yourself a combination of A+B data). Result D
Then link C and D, with an OUTER JOIN, make sure D is the outer source so you get all D records regardless of it finding a match in C or not. Ensure you have the ID from C and D in the output.
Then put in a query that restricts to C.ID IS NULL (so anything that is in C, i.e. in both tables, is excluded)
see Outer join and data filtering and Using sub-queries for some questions on left joins to exclude based on a second table)
HuwD 
(BOB member since 2007-04-19)
Thanks very much that worked a treat
Shell
Shell (BOB member since 2007-04-18)