Multiple Table Join Issue

P

PAR

In a payroll system there are 4 tables

One to many relationship
Check Summary (cs) - Master
Hours & Earnings (h&e)
Taxes (t)
Deductions (d)

Query =

Select cs.lastname, cs. firstname, cs.middlename, cs.clockno, cs.paydate,
cs.payperiod, he.paycodedescript, he.paycode, he.currentearnings,
t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct

right join cs on cs.csid = he.linktocsid
right join t.linktocsid = cs.csid
right join d.linkto csid = cs.csid

where cs.paydate >=(start of date range) and cs.paydate <=(end of date range)

The result looks like this

regpay 1 fedtax med
holiday 2 fedtax med
vac 3 fedtax med
regpay 1 statetax med
holiday 2 statetax med
vac 3 statetax med
regpay 1 fedtax dent
holiday 2 fedtax dent
vac 3 fedtax dent
regpay 1 statetax dent
holiday 2 statetax dent
vac 3 satetax dent

(of course there are many other paycodes, taxes and deductions) what I want
to see is

regpay
holiday
vac
fedtax
statetax
med
dent

How do I adjust the statement to create the correct join
Thank you
 
S

Smartin

In a payroll system there are 4 tables

One to many relationship
Check Summary (cs) - Master
Hours & Earnings (h&e)
Taxes (t)
Deductions (d)

Query =

Select cs.lastname, cs. firstname, cs.middlename, cs.clockno, cs.paydate,
cs.payperiod, he.paycodedescript, he.paycode, he.currentearnings,
t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct

right join cs on cs.csid = he.linktocsid
right join t.linktocsid = cs.csid
right join d.linkto csid = cs.csid

where cs.paydate >=(start of date range) and cs.paydate <=(end of date range)

The result looks like this

regpay 1 fedtax med
holiday 2 fedtax med
vac 3 fedtax med
regpay 1 statetax med
holiday 2 statetax med
vac 3 statetax med
regpay 1 fedtax dent
holiday 2 fedtax dent
vac 3 fedtax dent
regpay 1 statetax dent
holiday 2 statetax dent
vac 3 satetax dent

(of course there are many other paycodes, taxes and deductions) what I want
to see is

regpay
holiday
vac
fedtax
statetax
med
dent

How do I adjust the statement to create the correct join
Thank you
Click to expand...

Would you please repost the question with a complete SQL statement
(including the complete FROM clause) that generates the sample output?
I suspect what you need is simple aggregation in the query but it's
difficult to tell from above. Thanks!
 
P

PAR

Select cs.lastname, cs. firstname, cs.middlename, cs.clockno, cs.paydate,
cs.payperiod, he.paycodedescript, he.paycode, he.currentearnings,
t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct

from cs, he, t, d

right join cs on cs.csid = he.linktocsid
right join t.linktocsid = cs.csid
right join d.linkto csid = cs.csid

where cs.paydate >=(start of date range) and cs.paydate <=(end of date range)
 
P

PAR

Maybe a little more accurate:

Select cs.lastname, csfirstname, cs.middlename, cs.clockno, cs.paydate,
cs.payperiod, he.paycodedscript, hepaycode, he.currentearnings,
t.taxdescript, t.currenttax, d.deductdescript, d.currentdeduct

from cs right join he on cs.csid = he.linktocsid right join
t on cs.csid = t.linktocsid right join
d on cs.csid = d.linktocsid

where cs.paydate >=(start of date range) and cs.paydate <=(end of date range)
 
Top