Re: ISO: sql technique to select information from more than one table
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 10 Mar 2009 16:58:42 +0100
Message-ID: <49b68e0f$0$198$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
>> 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- Hide quoted text -
>>
>> - Show quoted text -
>> Thank you so much for your suggestion. I really appreciate you taking
>> the time to post a reply.
>> When I take your suggestion and plug in my information, oracle sqlplus
>> seems to not like the suggestion, but doesn't
>> produce a message saying why.
>> SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 10 10:11:18 2009
>> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>> Connected to:
>> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
>> Production
>> With the Partitioning and Data Mining options
>> 5 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
>> 6 csi_security.person_id=csi_core.sec_person_id and
>> 7 csi_hr.first_name <> nvl(csi_security.first_name,'Not Avail')
>> 8 or csi_hr.middle_name <> nvl(csi_security.initials,'Not Avail')
>> 9 or csi_hr.last_name <> nvl(csi_security.last_name,'Not Avail')
>> 10 union 'csi_telecom' source,
>> 11 csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
>> 12 csi_telecom.first_name, csi_telecom.middle_name,csi_telecom.last_name
>> 13 from csi_hr,csi_telecom,csi_core
>> 14 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
>> 15 csi_telecom.person_id=csi_core.sec_person_id and
>> 16 csi_hr.first_name <> nvl(csi_telecom.first_name,'Not Avail') or
>> 17 csi_hr.middle_name <> nvl(csi_telecom.middle_name,'Not Avail') or
>> 18* csi_hr.last_name <> nvl(csi_telecom.last_name,'Not Avail')
>>
>> SQL>
>> I can't see any obvious problem with my syntax, though I likely did
>> mess something up.
>> What error do you get?
>> I think you should put the part
>> within parentheses in both parts of your query
>> and there should be 'select' after the union (which I did forget too)
>> Shakespeare
>> ps Please respond in the newsgroup, in stead of mailing directly, so others can participate!
Date: Tue, 10 Mar 2009 16:58:42 +0100
Message-ID: <49b68e0f$0$198$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> On Mar 9, 5:01 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>> 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- Hide quoted text -
>>
>> - Show quoted text -
> > From an email >
>> Thank you so much for your suggestion. I really appreciate you taking
>> the time to post a reply.
>
>> When I take your suggestion and plug in my information, oracle sqlplus
>> seems to not like the suggestion, but doesn't
>> produce a message saying why.
>
>> SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 10 10:11:18 2009
>
>> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
> >
>> Connected to:
>> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
>> Production
>> With the Partitioning and Data Mining options
>>> 4 from csi_hr,csi_security,csi_core
>> 1 select 'csi_security' source,
>> 2 csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
>> 3 csi_security.first_name, csi_security.initials, csi_security.last_name
>> 5 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
>> 6 csi_security.person_id=csi_core.sec_person_id and
>> 7 csi_hr.first_name <> nvl(csi_security.first_name,'Not Avail')
>> 8 or csi_hr.middle_name <> nvl(csi_security.initials,'Not Avail')
>> 9 or csi_hr.last_name <> nvl(csi_security.last_name,'Not Avail')
>> 10 union 'csi_telecom' source,
>> 11 csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
>> 12 csi_telecom.first_name, csi_telecom.middle_name,csi_telecom.last_name
>> 13 from csi_hr,csi_telecom,csi_core
>> 14 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
>> 15 csi_telecom.person_id=csi_core.sec_person_id and
>> 16 csi_hr.first_name <> nvl(csi_telecom.first_name,'Not Avail') or
>> 17 csi_hr.middle_name <> nvl(csi_telecom.middle_name,'Not Avail') or
>> 18* csi_hr.last_name <> nvl(csi_telecom.last_name,'Not Avail')
>>
>> SQL>
>
>> I can't see any obvious problem with my syntax, though I likely did
>> mess something up.
> > > ---
>> What error do you get?
>
>> I think you should put the part
>
>> csi_hr.first_name <> nvl(csi_security.first_name,'Not Avail')
>> 8 or csi_hr.middle_name <> nvl(csi_security.initials,'Not Avail')
>> 9 or csi_hr.last_name <> nvl(csi_security.last_name,'Not Avail')
>
>> within parentheses in both parts of your query
> >
>> and there should be 'select' after the union (which I did forget too)
>
>> Shakespeare
>
>> ps Please respond in the newsgroup, in stead of mailing directly, so others can participate!
> > Sorry - I will respond here as requested. > > 1. I don't actually get an error - sqlplus just sits there as if it > did what I asked. > > So, I made the changes you suggested, and sqlplus continues to > indicate that it has read the select, but provides no indication that > it has executed it or if there is an error, what the error is: > > 1 select 'csi_security' source, > 2 csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name, > 3 csi_security.first_name, csi_security.initials, > csi_security.last_name > 4 from csi_hr,csi_security,csi_core > 5 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and > 6 csi_security.person_id=csi_core.sec_person_id and > 7 (csi_hr.first_name <> nvl(csi_security.first_name,'Not > Avail') > 8 or csi_hr.middle_name <> nvl(csi_security.initials,'Not > Avail') > 9 or csi_hr.last_name <> nvl(csi_security.last_name,'Not > Avail') ) > 10 union select 'csi_telecom' source, > 11 csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name, > 12 csi_telecom.first_name, csi_telecom.middle_name, > csi_telecom.last_name > 13 from csi_hr,csi_telecom,csi_core > 14 where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and > 15 csi_telecom.person_id=csi_core.sec_person_id and > 16 (csi_hr.first_name <> nvl(csi_telecom.first_name,'Not > Avail') or > 17 csi_hr.middle_name <> nvl(csi_telecom.middle_name,'Not > Avail') or > 18* csi_hr.last_name <> nvl(csi_telecom.last_name,'Not > Avail') ) > SQL>
Maybe you can post the outcome of this query here?
select 'csi_security' source,
csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name, csi_security.first_name, csi_security.initials, csi_security.last_name from csi_hr,csi_security,csi_core where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and csi_security.person_id=csi_core.sec_person_id
Shakespeare Received on Tue Mar 10 2009 - 10:58:42 CDT