Re: ISO: sql technique to select information from more than one table
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 10 Mar 2009 17:47:08 +0100
Message-ID: <49b69967$0$186$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
>> 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
>>>> 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 '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- Hide quoted text -
>>
>> - Show quoted text -
Date: Tue, 10 Mar 2009 17:47:08 +0100
Message-ID: <49b69967$0$186$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> On Mar 10, 11:58 am, Shakespeare <what..._at_xs4all.nl> wrote:
>> 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
>>>> 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 '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- Hide quoted text -
>>
>> - Show quoted text -
> > When I paste that select in, I get output from the 1558 rows selected, > with the security and hr data on the same line. There is no error or > probleml executing the select.
I Think you are already there, it should work now if you use the union and no empty lines. If you get no results back, it's the <>nvl part that causes it.
Shakespeare Received on Tue Mar 10 2009 - 11:47:08 CDT