Tricky inner Join

  • I have a table that has a vendors Order Number that could reference two different fields. I would like to get these two queries into one

    Neither field in sales is ever Null.

    SELECT [Tracking_Code]

    ,tracking.[Order_Code]

    ,Sales_Amount

    FROM Tbl_Tracking Tracking

    INNER JOIN Tbl_Sales Sales

    ON Sales.Your_reference = Tracking.Order_Code

    SELECT [Tracking_Code]

    ,Tracking.[Order_Code]

    ,Sales_Amount

    FROM Tbl_Tracking Tracking

    INNER JOIN Tbl_Sales Sales

    ON Sales.Order_Code = Tracking.Order_Code

  • Just join with an OR ...

    SELECT [Tracking_Code]

      ,tracking.[Order_Code]

      ,Sales_Amount

    FROM Tbl_Tracking Tracking

    INNER JOIN Tbl_Sales Sales

    ON (Sales.Your_reference = Tracking.Order_Code OR

         Sales.Order_Code = Tracking.Order_Code ) 

  • You need to understand your data and establish proper FK relationships on INT columns when you INSERT the data.

    It's too late to do it when you do SELECT.

    Why?

    1) You do INSERT once for each set of data, and SELECT - hundreds times per day;

    2) Performance of your SELECT queries will dramatically increase;

    3) You would avoid errors when both Sales.Your_reference and Sales.Order_Code occasionally contain the same value which in one case suppose to match Tracking.Order_Code and in another case suppose to match something else but not Tracking.Order_Code.

    Unfortunately you cannot get proper and reliable result from your tables unless you inbuild kind of scientific research into your SELECT. You can do it, but your clients must be prepared to wait until that research is completed.

    _____________
    Code for TallyGenerator

  • The OR statement worked but the performance changed from 2 min to 42 min query. I also found that there is some duplication in the Sales data between your_reference and Order_No. Seems to me I will have to design a feed into a warehouse table so I can join Correctly. Thanks for the feedback both answers are appreciated

  • Here's a try at it not knowing how all of it works.  This is assumming that the sales.your_reference and sales.order_code are the same in some of the records and not in others.  I propose using a union on the sales table and combining your results.  A union will eliminate dups.

    SELECT [Tracking_Code]

    ,tracking.[Order_Code]

    ,Sales_Amount

    FROM Tbl_Tracking Tracking

    INNER JOIN

    (

        select

             your_reference as order_code

        ,    sales_amount

        from tbl_sales

        union

        select

            order_code as order_code

        ,   sales_amount

        from tbl_sales

    )AS  sales

    ON Sales.Your_reference = Tracking.Order_Code

    Hope it gives you somewhere to go with it.

    Tom

  • The decrease in performance is probably because of the OR in the join.

    If you have opportunity to look at the plan, I'd guess that you'd find a *huge* worktable the size of the cartesian product of the two tables that are OR-joined.

    /Kenneth

Viewing 6 posts - 1 through 5 (of 5 total)

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