Query / Join Question

From: daecc <daecc96_at_yahoo.com>
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

Original text of this message