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

Larry W. Virden schreef:
On Mar 10, 11:58 am, Shakespeare <what...@xxxxxxxxx> wrote:
Larry W. Virden schreef:





On Mar 9, 5:01 pm, Shakespeare <what...@xxxxxxxxx> 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
.

Relevant Pages

  • Re: ISO: sql technique to select information from more than one table
    ... Just did some quick reading of ypur post, but does this do what you ask? ... union 't3' source, t4.key0, etc.. ... seems to not like the suggestion, ... I don't actually get an error - sqlplus just sits there as if it ...
    (comp.databases.oracle.misc)
  • Re: ISO: sql technique to select information from more than one table
    ... T4 maps the keys from one set of data to another. ... Does anyone have a suggestion for fixing the select so that it does ... union 't3' source, t4.key0, etc.. ... I don't actually get an error - sqlplus just sits there as if it ...
    (comp.databases.oracle.misc)
  • Re: HP 48GX ON key problem
    ... The reason would be in yur case that the main board is not making ... I tried your suggestion of pressing ... above the B and C keys and using the on/off button. ... but maybe its better than opening ...
    (comp.sys.hp48)
  • Re: Pour des fêtes non-offensantes
    ... >> C'est juste une suggestion. ... > légalisation de l'union des gens de même sexe, ... Previous by thread: ...
    (soc.culture.quebec)
  • Re: OT: The CATV Planning Department
    ... I think in that particular establishment just being in a union would ... thought I might try working as a labourer in Bristol. ... Ah darsn't do thaat, moy babby, nart wi'out orrderrs!" ... suggestion that we rang in for "orrderrs" met with a similar negative ...
    (uk.tech.digital-tv)