Difference Query

[kaushalneo]

I have two tables. One table is a subset of the other. I want to select only those rows in Table 2 that do not appear in Table 1. There are certain important points here -

1. Table 1 and Table 2 both have repetition in rows and do not have any kind of key that differentiates between them.

2. Table 1 may have some rows that appear only once in Table 1 but more than once in Table 2. In my result set, I want only those rows that are not in Table 1. So if a row in Table 1 appears two times, but appears 5 times in Table 2, I want to have it 3 times in Table 2.



I am not able to figure out how to do that. The unmatch query removes all identical rows from Table 2 even if it appears only once in Table 1 but 5 times in Table 2.

Can someone suggest a method to do this?

Your help will be greatly appreciated.

Thanks!

[rpeare]

how do you know which three of the five records you want to be left in table2 after the matching is done? Can you identify them through your data can you give an example of your data in both tables and what you want the end result to look like and, if it's not readily apparent, how you are deciding which records to eliminate from table 2 because they have a 'matching' record in table1.