Hi, I'm trying to join three (actually more) tables together with the purpose of getting user id and sums of various integers. Here's what I'm doing with a two table join, which works fine.
SELECT pcs.csr_sbcuid, SUM(b.tkts_scrnd)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd b
WHERE pcs.csr_lmos *= b.lmos AND pcs.themonth *= b.themonth AND pcs.theyear *= b.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;
I do an outer join on the pcs table because I would ultimately like to incorporate other statistics tables (those beginning with bo_) and those tables may or may not have an entry for a given user id. As soon as I add a third table, like in the example below, the sums go through the roof. I thought it had to do with grouping, but I've tried all kinds of combinations to no avail. Does anyone know if I can accomplish this with one query? Right now I'm doing about six queries of two table joins -- blegh! TIA!
SELECT DISTINCT pcs.csr_sbcuid, SUM(bo1.tkts_scrnd), SUM(bo2.qual_disp),
SUM(bo2.non_qual_disp), SUM(bo2.total_disp)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd bo1, bo_non_qual_disp bo2
WHERE pcs.csr_lmos *= bo1.lmos AND pcs.themonth *= bo1.themonth AND pcs.theyear *= bo1.theyear
AND pcs.csr_lmos *= bo2.lmos AND pcs.themonth *= bo2.themonth AND pcs.theyear *= bo2.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;
SELECT pcs.csr_sbcuid, SUM(b.tkts_scrnd)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd b
WHERE pcs.csr_lmos *= b.lmos AND pcs.themonth *= b.themonth AND pcs.theyear *= b.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;
I do an outer join on the pcs table because I would ultimately like to incorporate other statistics tables (those beginning with bo_) and those tables may or may not have an entry for a given user id. As soon as I add a third table, like in the example below, the sums go through the roof. I thought it had to do with grouping, but I've tried all kinds of combinations to no avail. Does anyone know if I can accomplish this with one query? Right now I'm doing about six queries of two table joins -- blegh! TIA!
SELECT DISTINCT pcs.csr_sbcuid, SUM(bo1.tkts_scrnd), SUM(bo2.qual_disp),
SUM(bo2.non_qual_disp), SUM(bo2.total_disp)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd bo1, bo_non_qual_disp bo2
WHERE pcs.csr_lmos *= bo1.lmos AND pcs.themonth *= bo1.themonth AND pcs.theyear *= bo1.theyear
AND pcs.csr_lmos *= bo2.lmos AND pcs.themonth *= bo2.themonth AND pcs.theyear *= bo2.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;