Re: ISO: sql technique to select information from more than one table
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 09 Mar 2009 22:01:02 +0100
Message-ID: <49b5838f$0$183$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> I have the following set of tables and columns
>
> 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.
Date: Mon, 09 Mar 2009 22:01:02 +0100
Message-ID: <49b5838f$0$183$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> I have the following set of tables and columns
>
> 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.
Just did some quick reading of ypur post, but does this do what you ask?
Select 't2' source, t4.key0, ... some values here...
from t1,t2,t4
where t1.id=t4.key1 and t2.id=t4.key3
and t1.name <> nvl(t2.name,'xxx')
union 't3' source, t4.key0, etc..
from t1,t3,t4
where t1.id=t4.key1 and t3.id=t4.key3 and
t1.name <> nvl(t3.name,'xxx')
If not having an name in t2 or t3 means t2, t3.name is null you have to take that into account as well, that's why the nvl(...'xxx') is there (supposing no one is called 'xxx' of course)
Shakespeare Received on Mon Mar 09 2009 - 16:01:02 CDT