[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Left Outer Join in SQR
- Subject: Left Outer Join in SQR
- From: Norman Dolph <NEDOLPH@AOL.COM>
- Date: Tue, 9 Nov 1999 19:32:07 EST
The Problem:
Assume Table "T" holds data for BOTH Employees AND Applicants - 4 rows.
T.SSN T.EventDate
111-11-1111 1999-08-01
111-11-1111 1999-11-01
222-22-2222 1999-08-01
333-33-3333 1999-08-01
444-44-4444 1999-08-01
Table "Z" holds ONLY Employees, is keyed on the SSN, and EFFECTIVE DATED.
(No rows for Applicants, 1 or More Effective dated rows Per Employee.
Z.SSN Z.EffDate Z.Status (never null or empty)
111-11-1111 1999-10-01 'OK'
111-11-1111 1999-01-01 'NG'
222-22-2222 1999-11-01 'A1'
333-33-3333 1999-06-01 'XX'
333-33-3333 1999-01-01 'OK'
The Query should return the status of each person when the event happened.
If an Employee, show the status on the EventDate
If an Applicant, show the row with the status field empty
(... thus an empty status field will indicate who was an applicant at the
time. even though he may be an employee NOW.)
This is the desired answer:
T.SSN T.EventDate Z.Status
111-11-1111 1999-08-01 'NG'
111-11-1111 1999-11-01 'OK'
222-22-2222 1999-08-01 (null, empty, blank)
333-33-3333 1999-08-01 'XX'
444-44-4444 1999-08-01 (null, empty, blank)
In SQR I have tried everything, including NOT EXISTS in a subquery on Table Z.
I either get a Cartesian join of everybody and everything, OR;
I can ONLY RETRIEVE ROWS FROM THE EMPLOYEES, i.e. the rows with a status.
--- Applicants at event time are always missing! ---
How can I make a Left Outer Join In SQR that will return the answer above.
Or any other trick of mastery you have used to solve this kind of thing.
Warmest thanks for any help,
Norman Dolph
(SQR for PeopleSoft 7.01 on DB2)