SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query help: OUTER JOIN with GROUP BY clause


Query help: OUTER JOIN with GROUP BY clause

Author
Message
yuvalherziger
yuvalherziger
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
yuvalherziger
yuvalherziger
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Mohit Dhiman
Mohit Dhiman
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 267
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...

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search