Query / Join Question
Date: 26 Aug 2002 13:35:26 -0700
Message-ID: <e6981358.0208261235.55acdcf8_at_posting.google.com>
I need to know the best view that will return:
1) All Employee records and any associated payment records for the
paydate
2) All Payment records and the most recent Employee record previous or
equal to the Payment paydate.
Table 1: Employee(emp_id, paydate, name, sick_days, ......) Zero or one Employee record will be created for each paydate. This table has about 250,000 records
Table 2: Payment(emp_id, paydate, amount, ......) Zero to many Payment records will be created for each paydate. This table has about 6,000,000 records.
Employee
10, 12/26/01, Bob, 7
11, 12/12/01, Betty, 5
Payment
10, 11/28/01, 200 11, 12/12/01, 400 11, 12/26/01, 500
Results I need from above sample data:
E.emp_id E.paydate E.name E.sick_days P.emp_id P.paydate
P.amount
-------- --------- ------ ----------- -------- ---------
10 12/26/01 Bob 7 (null) (null) (null) 11 12/12/01 Betty 5 11 12/12/01 400 (null) (null) (null) (null) 10 11/28/01 200 11 12/12/01 Betty 5 11 12/26/01 500
I have 3 queries UNIONed together that gives the results I need:
"Return all employee recs and associated payment recs, if any"
UNION
"Return all payment recs and most recent employee record previous to
paydate, if any"
UNION
"Return all payment recs that do not have an employee record previous
to payment paydate"
Anyone have any better ideas? Received on Mon Aug 26 2002 - 22:35:26 CEST