Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query help: OUTER JOIN with GROUP BY clause Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 4:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 12, 2014 4:25 AM
Points: 2, Visits: 7
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.
Post #1512200
Posted Thursday, November 7, 2013 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 12, 2014 4:25 AM
Points: 2, Visits: 7
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.
Post #1512209
Posted Saturday, December 28, 2013 4:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:30 AM
Points: 257, Visits: 112
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...
Post #1526282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse