January 10, 2012 at 4:17 am
Hello All,
I have the below query:
Select count(*) from blobs where blob_id in
(Select blob_id from attachments a,Purchase_orders P where
a.Purchase_order_id=P.purchase_order_id and is_Submitted=0 and is_Rejected=0
and year(Created_date)<=2010).
This query returns me 1913 while when I run the same query yearwise like
Select count(*) from blobs where blob_id in
(Select blob_id from attachments a,Purchase_orders P where
a.Purchase_order_id=P.purchase_order_id and is_Submitted=0 and is_Rejected=0
and year(Created_date)=2004)
i get a total of 1922
02004
2652005
6092006
9152007
1232008
92009
12010
Could someone let me know why this difference.
Thanks in advance.
Regards,
K. Sripriya
January 10, 2012 at 4:52 am
You're comparing where the Year is less than 2010 or where the Year equals 2004. There are five other year values possible in the first query than the second query. I'm not at all surprised that you're seeing differences in the results.
Also, you should look at using ANSI standard join methodologies. That old ANSI 89 style join syntax is very outdated and won't work at all for outer joins when you get to SQL Server 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2012 at 4:57 am
Hi,
thank you for your response. You didnt get my point. I have mentioned, I would be having 7 more queries for each year like the second query and have given the output in a tabular format.
January 10, 2012 at 5:00 am
Can you give the definition of the 3 tables involved ?
January 10, 2012 at 5:03 am
I think you have something like :
blob_id comes from attachment.
Created_date comes from Purchase_Orders
You have 2 rows in Purchase_Orders linked to the same attachment but having different CReated_Date (different years)
When you run a single query, duplicates are removed because of IN.
When you run distinct queries, duplicates are still there.
January 10, 2012 at 5:20 am
The thing is, you're assuming that your data has no rows below 2004. I'll bet it's not what you think it is. Logically those queries are not equivalent. Best thing to do is compare the results rather than comparing the counts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2012 at 5:34 am
Try picking up all of the year values in one query:
SELECT d.Create_year, COUNT(*)
from blobs b
INNER JOIN (
Select blob_id, Create_year = year(Created_date)
from attachments a
INNER JOIN Purchase_orders P ON a.Purchase_order_id = P.purchase_order_id
where is_Submitted=0 and is_Rejected=0
) d
ON d.blob_id = b.blob_id
GROUP BY d.Create_year
ORDER BY d.Create_year
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply