Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Trouble joining multiple tables

    Hi,
    This seems like a basic problem but I can't figure out how to resolve it.

    I have a query :
    Code:
    SELECT     PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
    FROM         PR LEFT OUTER JOIN
                          LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
                          LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
    WHERE     (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
    GROUP BY PR.WBS2, LB.AmtBud
    ORDER BY PR.WBS2
    The output of the above query:
    Code:
    WBS2	Expr5	budget
    0141		
    0141	95	3000
    0143		
    1217	24540	
    1217		500
    1217	622.5	800
    1217		1000
    1217		2000
    1217		4000
    1217		4500
    1217	7290	8000
    1217	935	13500
    1217	34450	18000
    1217	65960	32000
    1217	38010	44000
    1218	38100	
    1219	13224.5	
    1220		
    1221		
    1222	62000	
    1222		4000
    1223	12702
    I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:
    Code:
    0141	 						
    	0141	01	0141	01	0141	01	0	3000
    	0141	01	0141	01	0141	01	47.5	3000
    	0141	01	0141	01	0141	01	47.5	3000
    	0143	 						
    	0143	05						
    	1217	 						
    	1217	010	1217	010	1217	010	0	8000
    	1217	010	1217	010	1217	010	0	8000
    	1217	010	1217	010	1217	010	0	8000
    	1217	010	1217	010	1217	010	0	8000
    	1217	010	1217	010	1217	010	1017.5	8000
    	1217	010	1217	010	1217	010	382.5	8000
    	1217	010	1217	010	1217	010	27.5	8000
    	1217	010	1217	010	1217	010	302.5	8000
    	1217	010	1217	010	1217	010	27.5	8000
    	1217	010	1217	010	1217	010	382.5	8000
    	1217	010	1217	010	1217	010	302.5	8000
    	1217	010	1217	010	1217	010	495	8000
    	1217	010	1217	010	1217	010	200	8000
    	1217	010	1217	010	1217	010	1017.5	8000
    	1217	010	1217	010	1217	010	1182.5	8000
    	1217	010	1217	010	1217	010	1952.5	8000
    	1217	060						
    	1217	061						
    	1217	080			1217	080		4000
    So as a result I am getting 9000 where wbs2 = '0141'

    I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?

    Thank You.


  2. #2
    Join Date
    May 2004
    Posts
    105
    OK, I think I figured it out. I probably should have put SUM(Distinct lb.amtbud) to single out only one of the values. maybe this will work

  3. #3
    Join Date
    May 2004
    Posts
    105
    Oh no! I just realized what if there are more than one amounts that are duplicates. I have to group this query by wbs2 so does any one have any ideas how to do this? I don't want to use a subquery but I can use a case statement.

    Thanks
    Last edited by lauramccord; 12-15-04 at 15:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •