Post reply

HELP with a query

  • March 10, 2012 at 8:55 am

    #258388

    I have a query that is really breaking my chops.

    I am joining three tables, PRD_Header, PRD_summary, PRD_Unit

    Each table has prdkey in common

    However, in PRD_unit, the stockID may be found in any ONE of the 6 stockID positions, but will only appear once per row.

    Problem: the query is returning WAY too many results. Should return about 10 rows but instead is returning over 900 rows.

    When I search the results, no prdkey is duplicated, but most of the order_date are outside the restrictions of the WHERE clause.

    Any help or insight would be greatly appreciated.

    Thanks.

    SELECT PRD_HEADER.prdkey, PRD_summary.prdkey,

    PRD_unit.stockID1,PRD_unit.stockID2,PRD_unit.stockID3,

    PRD_unit.stockID4,PRD_unit.stockID5,PRD_unit.stockID6

    FROM PRD_HEADER INNER JOIN PRD_Summary ON PRD_HEADER.PrdKey = acc_summary.PrdKey

    INNER JOIN PRD_Unit ON PRD_Summary.PrdKey = PRD_Unit.PrdKey

    where PRD_Summary.Order_Date BETWEEN '01/06/2012' AND '01/07/2012'

    AND PRD_HEADER.Status != '0'

    AND PRD_Summary.OrderSent = 'Y'

    AND PRD_summary.StoreCode ='03301'

    AND PRD_unit.stockID1='6001a'

    OR PRD_unit.stockID2 ='6001a'

    OR PRD_unit.stockID3='6001a'

    OR PRD_unit.stockID4='6001a'

    OR PRD_unit.stockID5='6001a'

    OR PRD_unit.stockID6='6001a'

    ORDER BY PRD_HEADER.prdkey

  • March 10, 2012 at 10:14 am

    #1457506

    Your query acts upon 3 main columns PRD_HEADER.prdkey, PRD_summary.prdkey & PRD_Unit.PrdKey. You selected only PRD_HEADER.prdkey and PRD_summary.prdkey and u said u did not have any dups; so PRD_HEADER.prdkey and PRD_summary.prdkey are 1 : 1. But what about PRD_summary.prdkey & PRD_Unit.PrdKey ??? I bet they are not in 1 : 1 !!

    Try checking for duplicates in PRD_Unit.PrdKey!

  • March 10, 2012 at 10:20 am

    #1457507

    PRD_UNIT could potentially have more than one row per prdkey. It would be rare though.

    I'm beginning to think that the OR clauses are allowing the query to go outside of the date confinement in the WHERE clause.

  • March 10, 2012 at 10:35 am

    #1457510

    may be, but i doubt it. Without seeing data, we really cant guess on whats worng! is it possible to get data or atleast mocked-up data?

  • March 10, 2012 at 11:36 am

    #1457517

    No, the data is too big at this point. I could send you the result set clearly showing results outside of the date restrection

  • March 10, 2012 at 11:47 am

    #1457518

    FROM PRD_HEADER INNER JOIN PRD_Summary ON PRD_HEADER.PrdKey = acc_summary.PrdKey

    INNER JOIN PRD_Unit ON PRD_Summary.PrdKey = PRD_Unit.PrdKey

    Check this Join Condition...

    Shound you be joining PRD_HEADER.PrdKey = PRD_summary.PrdKey instead of PRD_HEADER.PrdKey = acc_summary.PrdKey

  • March 10, 2012 at 4:27 pm

    #1457526

    Thanks dad, I'll be home by 10.

  • March 11, 2012 at 11:24 am

    #1457610

    No, sorry. That is a typo, should be PRD_summary.

  • March 11, 2012 at 8:46 pm

    #1457663

    without digging too deep (sometimes the simplest solutions are the best) - try to isolate the "OR" conditions from the rest of the WHERE clause...

    SELECT PRD_HEADER.prdkey, PRD_summary.prdkey,

    PRD_unit.stockID1,PRD_unit.stockID2,PRD_unit.stockID3,

    PRD_unit.stockID4,PRD_unit.stockID5,PRD_unit.stockID6

    FROM PRD_HEADER INNER JOIN PRD_Summary ON PRD_HEADER.PrdKey = acc_summary.PrdKey

    INNER JOIN PRD_Unit ON PRD_Summary.PrdKey = PRD_Unit.PrdKey

    where PRD_Summary.Order_Date BETWEEN '01/06/2012' AND '01/07/2012'

    AND PRD_HEADER.Status != '0'

    AND PRD_Summary.OrderSent = 'Y'

    AND PRD_summary.StoreCode ='03301'

    AND

    ( PRD_unit.stockID1='6001a' OR PRD_unit.stockID2 ='6001a' OR PRD_unit.stockID3='6001a' OR PRD_unit.stockID4='6001a' OR PRD_unit.stockID5='6001a' OR PRD_unit.stockID6='6001a' )

    ORDER BY PRD_HEADER.prdkey

    ain't pretty, but should to the trick...

  • March 12, 2012 at 6:59 am

    #1457808

    Based on this comment, it looks like you need to add parens to your query.

    ken def

    I'm beginning to think that the OR clauses are allowing the query to go outside of the date confinement in the WHERE clause.

    Since AND takes precedence over OR, your query is equivalent to the following.

    SELECT PRD_HEADER.prdkey, PRD_summary.prdkey,

    PRD_unit.stockID1,PRD_unit.stockID2,PRD_unit.stockID3,

    PRD_unit.stockID4,PRD_unit.stockID5,PRD_unit.stockID6

    FROM PRD_HEADER INNER JOIN PRD_Summary ON PRD_HEADER.PrdKey = acc_summary.PrdKey

    INNER JOIN PRD_Unit ON PRD_Summary.PrdKey = PRD_Unit.PrdKey

    where ( PRD_Summary.Order_Date BETWEEN '01/06/2012' AND '01/07/2012'

    AND PRD_HEADER.Status != '0'

    AND PRD_Summary.OrderSent = 'Y'

    AND PRD_summary.StoreCode ='03301'

    AND PRD_unit.stockID1='6001a')

    OR PRD_unit.stockID2 ='6001a'

    OR PRD_unit.stockID3='6001a'

    OR PRD_unit.stockID4='6001a'

    OR PRD_unit.stockID5='6001a'

    OR PRD_unit.stockID6='6001a'

    ORDER BY PRD_HEADER.prdkey

    I think you actually want the following:

    SELECT PRD_HEADER.prdkey, PRD_summary.prdkey,

    PRD_unit.stockID1,PRD_unit.stockID2,PRD_unit.stockID3,

    PRD_unit.stockID4,PRD_unit.stockID5,PRD_unit.stockID6

    FROM PRD_HEADER INNER JOIN PRD_Summary ON PRD_HEADER.PrdKey = acc_summary.PrdKey

    INNER JOIN PRD_Unit ON PRD_Summary.PrdKey = PRD_Unit.PrdKey

    where PRD_Summary.Order_Date BETWEEN '01/06/2012' AND '01/07/2012'

    AND PRD_HEADER.Status != '0'

    AND PRD_Summary.OrderSent = 'Y'

    AND PRD_summary.StoreCode ='03301'

    AND (PRD_unit.stockID1='6001a'

    OR PRD_unit.stockID2 ='6001a'

    OR PRD_unit.stockID3='6001a'

    OR PRD_unit.stockID4='6001a'

    OR PRD_unit.stockID5='6001a'

    OR PRD_unit.stockID6='6001a')

    ORDER BY PRD_HEADER.prdkey

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply