Multiple Table Join Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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!
 
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)
 
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)
 
Back
Top