November 7, 2013 at 4:59 am
I am querying two tables:
tblPurhcaseOrders - holds purchase orders with various columns, one of which is a foreign key called OrderTypeID
tblOrderTypes - holds 4 types of purchase orders
One of the order types in tblOrderTypes was added later than the first 3 basic ones. Thus, instances of purchase orders with this newer order type appear only from a certain point of time (Oct. 2012). I'm writing an aggregated query which should ultimately bring me the sum of all purchase orders, grouped by their type. I do want the results to me 0 or NULL, even if I query a point of time in which the newer order type had not existed yet.
SELECT MONTH(tblPurchaseOrders.DeliveryDate) AS sMonth,
Year(tblPurchaseOrders.DeliveryDate) AS sYear,
tblOrderType.OrderTypeName,
SUM(ISNULL(TotalPrice,0)) AS sValue
FROM tblPurchaseOrders RIGHT OUTER JOIN
tblOrderTypes ON tblPurchaseOrders.OrderTypeID = tblOrderTypes.OrderTypeID
WHERE MONTH(DeliveryDate) = 1 AND
YEAR(DeliveryDate) = 2012
GROUP BY MONTH(DeliveryDate), Year(DeliveryDate), tblOrderType.OrderTypeName
Now, the query above works just fine and provides me with correct aggregation, only it leaves out the newer order type (reminder, it didn't exist in JAN-2012 yet).
If I change the query condition to MONTH(DeliveryDate) = 2 AND YEAR(DeliveryDate) = 2012 it appears. I've tried full join and also tried switching between the order of the two tables and toggled to LEFT OUTER JOIN, no luck.
November 7, 2013 at 5:31 am
Found it.
When taking the conditions in the WHERE clause and moving to the conditions clause of the JOIN clause, the desired result is achieved.
December 28, 2013 at 4:11 am
That's right.
You need to be careful with OUTER JOINS when applying ON & WHERE conditions.
Both are filters but ON is applied before Outer rows are added.
This is the sequence :
1) CROSS JOIN applied
2) ON filter applied
3) Outer rows of preserved table added
4) WHERE filter applied
So even if you have outer rows in your intermediate result the WHERE filter will remove those which do not yield TRUE for it's clause...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply