Post reply

Strange Query Results

  • September 15, 2006 at 9:44 am

    #115729

    I'm creating a view and seem to be getting some strange results...

    Breaking the query down its most basic structure(s), the following happens:

    select count(*) from mainestimatedetails

    This gives x number of estimates.  This works fine.

    select count(*) from mainestimatedetails where id in (select estimateid from mainjobdetails)

    This gives the number of estimates that have been converted into jobs.  So far so good.

    select count(*) from mainestimatedetails where id not in (select estimateid from mainjobdetails)

    This is where the problem lies... On SOME of my databases (5 out of 7), this returns a 0 rather than the expected result (none of the dbs have a 100% conversion rate).

    Does anyone out there have any ideas on why this could be happening?  I know I can get around the issue by subtracting the converted quotes from the total number but I need to apply the un-ordered quotes figure to a more complex formula and it's getting a bit on the un-wieldy side.

    Thanks in advance for any help you can offer.

    Mat

  • September 15, 2006 at 10:28 am

    #660774

    I know that query should have worked. Can you try this

    select count(*) from mainestimatedetails where not exists (select 1 from mainjobdetails where mainestimatedetails.id =mainjobdetails.estimateid)

    Thanks

    Sreejith

  • September 15, 2006 at 10:31 am

    #660775

    I'll give it a shot 1st thing on Monday morning... Cheers

  • September 15, 2006 at 10:32 am

    #660777

    The in and not in clauses are being turned into join/outer join clauses by the optimizer:

    select count(*)

    from mainestimatedetails d, mainjobdetails j

    where d.id *= j.estimateid

    and j.estimateid is null

    Unfortunately, for SQL 2000 at least, it cannot properly handle the search criteria on an outer joined table.  You could use ANSI syntax:

    select count(*)

    from mainestimatedetails d outer join mainjobdetails j on d.id = j.estimateid

    where j.estimateid is null

    or, to make it work in T-SQL, use "not exists" with a joined sub-query instead:

    select count(*)

    from mainestimatedetails d

    where not exists (select 1 from mainjobdetails j where j.estimateid = d.id)

  • September 18, 2006 at 2:01 am

    #660961

    Cheers for that ... the NOT EXISTS works perfectly.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply