January 24, 2006 at 1:48 pm
I am new to SQL script and am trying to do a very simple query. I have three tables in a database and am trying to retrieve some records. The query executes fine but does not bring up any records although I know there should be 3 for the specified time period. I think the issue is with my use of the Join function but am not sure. Can someone help?
Here is the code:
select a.ItemName, (SUM(CAST(b.Quantity AS int)))as "Count"
from Items a
join Order_Details b on a.ItemID = b.ItemID
join Orders c on b.OrderID = c.OrderID
WHERE c.OrderDate between '11/11/2004' AND '11/30/2004'
GROUP by a.ItemName, a.ItemUnitPrice
Order by a.ItemName
January 24, 2006 at 2:24 pm
Hard to guess from the information you have provided.
The query looks fine if your trying to figure out the number of items that are ordered.
Post some sample data, and desired results
January 25, 2006 at 1:20 am
Try changing:
WHERE c.OrderDate between '11/11/2004' AND '11/30/2004'
to:
WHERE c.OrderDate between '20041111' AND '20041130'
If that does not work, comment:
--join Orders c on b.OrderID = c.OrderID
--WHERE c.OrderDate between '20041111' AND '20041130'
Then see if you get any rows. If you do maybe you have no orders within those dates?
Andy
January 25, 2006 at 2:06 am
Is OrderDate a datetime column or is it character (char, varchar) datatype?
If it is datetime, then try David's suggestion (you should always use this unambiguous format to avoid problems with various settings)... but also consider possibility that the column can contain time portion. If it does, and you use BETWEEN with no time specification, orders from 30.11.2004 will not be included. Date without time is considered to have time portion 00:00:00, and that means order processed at 20041130 05:12:01 gets excluded - the date is bigger than required. Best way to avoid problems with time and still preserve use of indexes is to add one day to upper limit and use >=, < instead of BETWEEN:
... WHERE c.OrderDate >= '20041111' AND c.OrderDate < '20041201'
but you can also do it this way:
... WHERE c.OrderDate between '20041111' AND '20041130 23:59:59.997'
If it is character type, then it is worse, since you'll have to convert it to datetime first to do any reasonable searches on it. If you can influence it, never use character datatypes for dates.
January 25, 2006 at 9:32 am
change the where part to this
WHERE c.OrderDate between '2004/11/11' and '2004/11/30'
it would help us all if your repond to the forum so that we know if something worked
it would also help you later on to start with the order table first in query because this might have an impact on your performance. if you start with the items table, it will get all the items and then go on from there.
January 25, 2006 at 9:38 am
I tried commenting the lines out and I retreived 4 records. I looked at the table and found that 3 records fall into the time I have specified.
Also, OrderDate is a datetime.
Any suggestions?
January 25, 2006 at 9:50 am
January 25, 2006 at 9:51 am
Could the order of your DateTime value be correct (or differently is the correct word). USA Datatime is mm/dd/yy, whilst UK is dd/mm/yy. Maybe you need to use the CONVERT function to convert the style of the Datetime data???
Such a posssibility...
Tryst
January 25, 2006 at 10:23 am
This may have already been considered, and without looking at any actual sample data (which you're probably not at liberty to post anyway) I can't be sure, but I've run into issues in the past with certain Crystal reports (number of accounts opened grouped by employee) leaving off a few items. It eventually turned out to be an issue with the JOINs involved... the data for some of the accounts was incomplete and so an INNER JOIN (the default when just JOIN is used) was excluding rows because some of the values in a JOINed table were NULL. In line with David Long's suggestion you may try running the query with a FULL OUTER JOIN to see if those 3 rows reappear in your results. Good luck.
Brian
January 25, 2006 at 11:22 am
I tried the full outer join and it did the same thing. Although, when I commented out the lines below it brought back one more record that had a value of null.
join Orders c on b.OrderID = c.OrderID
WHERE c.OrderDate between '11/11/2004' AND '11/30/2004'
The database is very small, so if anyone wants me to send it to them to take a look, I can.
January 25, 2006 at 11:44 am
Perhaps you should consider the table first that has all of the records, Orders. Then do Left Outer joins to it with the reasoning being that if we have an order, then we have order details, and if we have order details then we have items. I believe the problems could surface in the order of joins as well as the date_time conversions as stated above.
January 25, 2006 at 3:00 pm
I got it. I followed this format for joins:
FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey
Table number 2 in my database is Orders and the OrderID field is currently blank. This was my issue. I need to copy the contents of the column OrderDetailsID (primary key) into OrderID for the query to work.
Thanks for all your help. I appreciate it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply