SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Comparing Values in a GROUP BY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 06/19/2013 :  13:32:55  Show Profile  Reply with Quote
Good morning. Am trying to get my head around a problem with a query I wrote. The query compares a value on an order table to values in an attached table that shows parcels and compares the two for inequality. So, the number of pieces on an order might be '1' and the number of rows in the Parcels table for that order may be '2', and so the order is flagged in the query for review. So far so good, and the query appears to work fine.

However, the eventuality I did not account for was when the order exists and shows a value for number of pieces, but no rows exist in the parcels table. What is the best way to approach this problem? Here is my code: (Sorry, indentations are not appearing even though I put them in)

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT

O.OrderID,
O.OriginName,
O.ReadyTimeFrom,
O.Pieces AS 'Pieces On Order',
COUNT(P.OrderID) AS 'Parcels On Order'

FROM tblOrder AS O
INNER JOIN tblParcel as P ON P.OrderID = O.OrderID

WHERE
O.ReadyTimeFrom BETWEEN @Calendar1 AND @Calendar2 + 1
AND O.Status <> 4


GROUP BY
O.OrderID, O.OriginName, O.Pieces, P.OrderID, O.ReadyTimeFrom

HAVING O.Pieces <> Count(*)

ORDER BY P.OrderID DESC


Thanks!
Dan


Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 06/19/2013 :  13:47:38  Show Profile  Reply with Quote
SELECT 
   O.OrderID, 
   O.OriginName,
   O.ReadyTimeFrom,
   O.Pieces AS 'Pieces On Order', 
   COUNT(P.OrderID) AS 'Parcels On Order'
FROM tblOrder AS O 
LEFT OUTER JOIN tblParcel as P ON P.OrderID = O.OrderID
WHERE 
   O.ReadyTimeFrom BETWEEN @Calendar1 AND @Calendar2 + 1
   AND O.Status <> 4
GROUP BY 
   O.OrderID, O.OriginName, O.Pieces, P.OrderID, O.ReadyTimeFrom
HAVING O.Pieces <> Count(*) 
   OR COUNT(P.OrderID) = 0
ORDER BY P.OrderID DESC


BTW, you can use the tags CODE and /CODE (each surrounded by square brackets) around your code and it will preserve your formatting. Click on "Forum Code" in the left hand window of the Quick Reply box for additional details.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/19/2013 :  13:51:10  Show Profile  Reply with Quote
I don't know whether I understood your request properly.. however I guess you are looking for the solution where you've record in Orders table, but there is no reference rows in parcel table. In such case you could use Left Join instead Inner Join. This will bring record from order table even if there is no reference rows in parcel.

i.e. LEFT OUTER JOIN tblParcel as P ON P.OrderID = O.OrderID

Let me know if it helps.

==============================
I'm here to learn new things everyday..
Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 06/19/2013 :  14:55:23  Show Profile  Reply with Quote
Thank you very much. I supposed it's time for me to learn about OUTER JOINs!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000