ISO: sql technique to select information from more than one table

T1:
Key1
Name
Dept.

T2:
Key2
Name
Dept

T3:
Key2 (that is to say - the values here are the same "things" as table 2's key2)
Name
Dept

T4:
Key0
Key1 (values match T1.Key1)
Key2 (values match T2.Key2 and T3.Key2)

T4 maps the keys from one set of data to another. There should be rows containing all the keys.

T1 is in my case the master list of names. I want to find out if there are any rows in t1 where the person references also appears in t2 or t3, but with a different name column value.

For examine, the person may be Thomas in t1, Tom in t2 and Tommy in t3.

If the person's key is in t2, but no name is listed, I want to find that as well.

I tried something to the effect of

select t1.name,t1.dept, t2.name,t2.dept, t3.name, t3.dept from t1, t2, t3
where t1.key1 in (select key1 from t4) and

           ( (t2.key2 in (select key2 from t4 where key1 = t1.key1 and t1.name != t2.name)) or

             (t3.key2 in (select key2 from t4 where key1 = t1.key1 and t1.name != t3.name))

          )

however, the resulting column values are not what I am expected.

I have fiddled with the where clause a bit after reading several web pages about solving this kind of problem, and the above is where I am at now - still unsuccessful.

Does anyone have a suggestion for fixing the select so that it does what I am trying for - I want to see the names (and departments) where the rows should match, but are not matching. Received on Mon Mar 09 2009 - 13:37:57 CDT