Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Nested Query problem

    I have this query:
    Code:
    SELECT SUM(SEP_ADJ) AS SEP, SUM(OCT_ADJ) AS OCT
    FROM (
    	SELECT PRODUCT, SEP_ADJ, OCT_ADJ 
    	FROM PLAN_SHIP PL INNER JOIN BOD_HEADER BH ON PL.BOD_INDEX=BH.BOD_INDEX
    	WHERE [YEAR]=2009 AND SCEN_ID=1
    ) PL
    It returns:
    SEP | OCT
    7333 | 7458

    But when I add to it:
    Code:
    SELECT SUM(SEP_ADJ) AS SEP, SUM(OCT_ADJ) AS OCT
    FROM (
    	SELECT PRODUCT, SEP_ADJ, OCT_ADJ 
    	FROM PLAN_SHIP PL INNER JOIN BOD_HEADER BH ON PL.BOD_INDEX=BH.BOD_INDEX
    	WHERE [YEAR]=2009 AND SCEN_ID=1
    ) PL
    INNER JOIN (
    SELECT     PART_CODE AS PRODUCT, part_desc AS         PROD_DESC, part_grp AS PROD_CLASS, PART_TYPE AS PROD_TYPE
    	FROM         lawn_partmstr
    	UNION 
    	SELECT     PRODUCT, PROD_DESC AS PROD_DESC, PROD_CLASS, PROD_TYPE
    	FROM         PROD_MASTER
    	
    ) PM ON PL.PRODUCT=PM.PRODUCT
    I end up with HIGHER totals

    SEP | OCT
    7429 | 7555

    Of course, my original query is much larger than this, but I was able to narrow it down to this and still get the same results. It seems like the subquery I've added on is returning duplicates, but how could that be possible?
    Last edited by RedNeckGeek; 09-16-09 at 15:39.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It would seem your nested expression has a dup, as you kind of suggested. Try running this query:
    SELECT PRODUCT FROM
    (
    SELECT PART_CODE AS PRODUCT
    FROM lawn_partmstr
    UNION
    SELECT PRODUCT
    FROM PROD_MASTER
    )
    GROUP BY PRODUCT HAVING COUNT(*) > 1
    Dave

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yeah, there were a few dups. Boneheaded mistake on my part.
    Thanks
    Inspiration Through Fermentation

Posting Permissions

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