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

From: Larry W. Virden <lvirden_at_gmail.com>
Date: Tue, 10 Mar 2009 09:43:50 -0700 (PDT)
Message-ID: <14241a65-6ef6-4bbd-a29f-c7636a97b6d6_at_e15g2000vbe.googlegroups.com>



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. Received on Tue Mar 10 2009 - 11:43:50 CDT

Original text of this message