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:
> 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 Received on Tue Mar 10 2009 - 10:58:42 CDT

Original text of this message