Select query yielding diff. results

  • 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

  • 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

  • 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.

  • Can you give the definition of the 3 tables involved ?

  • 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.

  • 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

  • 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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/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