August 3, 2006 at 3:45 pm
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
August 3, 2006 at 4:04 pm
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 )
August 3, 2006 at 5:31 pm
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
August 4, 2006 at 8:52 am
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
August 4, 2006 at 1:35 pm
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
August 7, 2006 at 2:08 am
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