Selecting Unique values between 2 tables

[yngjzy12]

Hey, I've been trying to get this working for awhile now and haven't come up with anything. Here's a picture representing the data to give you a better idea.

Table1
-----------
PK Index1 Index2
1 C D
2 C F
3 C H

Table2
-----------
PK Index1 Index2
1 C D
2 C S

What I'm trying to do is union these two tables on Index1. That would give 5 results. After that I want to check if any rows in Table2 have any Index2 values that match those in Table1. In this case, there is 1. If there is a match, then discard the row in Table1, keeping the row in Table2 leaving us with 4 results. I've tried using the UNION, INNER/LEFT/RIGHT JOIN, experimenting with many different combination's but can't seem to get it. Anyone have any ideas? I'm not sure if this is poor table design or I'm just missing something in my SQL statement. Thanks

 

[r937]

you cannot union two tables "on" a particular column -- a union is performed on all columns

what does "discard the row in table 1" mean? after a UNION, there is only a result set, and no longer any "row in table 1"

yes, your table design does seem to be questionable, if these are permanent tables, because they appear to have the same layout/function/purpose

r937.com | rudy.ca