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.

Larry W. Virden schreef:
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

On Mar 9, 5:01*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
From an email



---

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>

Okay, here's the next thing that I have learned. I tried taking the
pieces of the select individually and executing them. When I did this,
the first select produces output. However, the second part of the
statement just stoped . interestingly enough, if I then type a / ,
the select occurs. So, I tried this version of my select, which I
tweaked to handle an issue with upper and lower case.

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 and
(upper(csi_hr.first_name) <> nvl(csi_security.first_name,'NOT
AVAIL')
or upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <> nvl
(csi_security.initials,'NOT AVAIL')
or upper(csi_hr.last_name) <> nvl(csi_security.last_name,'NOT
AVAIL') )
union select 'csi_telecom' source,
csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
csi_telecom.first_name, csi_telecom.middle_name,
csi_telecom.last_name
from csi_hr,csi_telecom,csi_core
where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
csi_telecom.person_id=csi_core.sec_person_id and
(upper(csi_hr.first_name) <> nvl(csi_telecom.first_name,'NOT
AVAIL') or
upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <>
nvl(csi_telecom.middle_name,'NOT AVAIL') or
upper(csi_hr.last_name) <> nvl(csi_telecom.last_name,'Not
Avail') )

;

sqlplus reads the above query and doesn't execute it. Then I type a /
and it does execute it.

This brings me much closer to my desired output, even if I do have to
mess around a bit in the sql script file.

The one thing that would be ideal is if I could 'merge' the outputs so
that instead of 2 reports, one after the other, I got
one report, with the 3 sets of names on one line.

I had expected that the union would do that.

Larry W. Virden schreef:
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

Larry W. Virden schreef:
That's indeed what the union would do. Check for empty lines in your
statement. There is one at the end, just before the ';'


Shakespeare

On Mar 10, 11:58*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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.

Larry W. Virden schreef:
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

On Mar 10, 12:19*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Ah - removing the blank line before the ; now actually causes the
select to execute. However, what I get is two lines - the first column
of the first line says "csi_security" followed by the csi_hr name and
then the csi_security name. Then, 1348 lines later, there is a second
line which says "csi_telecom" in the first column, followed by the
csi_hr name and then the csi_telecom name.



The sql in question is

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 and
(upper(csi_hr.first_name) <> nvl(csi_security.first_name,'NOT
AVAIL')
or upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <> nvl
(csi_security.initials
,'NOT AVAIL')
or upper(csi_hr.last_name) <> nvl(csi_security.last_name,'NOT
AVAIL') )
union select 'csi_telecom' source,
csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
csi_telecom.first_name, csi_telecom.middle_name,
csi_telecom.last_name
from csi_hr,csi_telecom,csi_core
where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and
csi_telecom.person_id=csi_core.sec_person_id and
(upper(csi_hr.first_name) <> nvl(csi_telecom.first_name,'NOT
AVAIL') or
upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <>
nvl(csi_telecom.middle_name,'NOT AVAIL') or
upper(csi_hr.last_name) <> nvl(csi_telecom.last_name,'Not
Avail') )
;

On Mar 10, 12:48*pm, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:


Let me clarify. I get 1700+ lines output. The first 1349 lines are the
results relating to csi_security. The last 356 lines are tagged as
being from the csi_telecom table.