July 1, 2010 at 12:37 pm
the below do not return the same results...i think it is because it is trying to say where person not in null which doesnt hold true. Is that correct?
select tt.*
from TempBaby.dbo.Admin2_543 tt (nolock)
WHERE tt.PersonId not IN (SELECT PersonId
FROM TempBaby.dbo.SuccessCals (nolock))
select *
from TempBaby.dbo.Admin2_543 tt
left join tempbaby.dbo.SuccessCals sc
on sc.personid = tt.personid
where sc.personid is null
I cannont provide the data here cause there are too many recs and i cannot get it to not match w/ a subset of recs. U will notice that they are written identically though. Any ideas why the first one doesnt retrun records and the second does? The second query is my version and is returning correct data. Just dont understand why the first is not.
July 1, 2010 at 12:56 pm
Edited: Sorry I did not see the bottom paragraph. well the query is reformated anyway.lol sorry I did not see your explanation at the bottom.
there seams to be two seperate queries here.
--Query 1
select tt.*
from TempBaby.dbo.Admin2_543 tt (nolock)
WHERE
tt.PersonId not IN
(
SELECT PersonId
FROM TempBaby.dbo.SuccessCals (nolock)
)
--Query 1 end
--Query 2
select *
from TempBaby.dbo.Admin2_543 tt
left join tempbaby.dbo.SuccessCals sc
on sc.personid = tt.personid
where sc.personid is null
--Query 2 end
In the second query If there are no null personid's in TempBaby.dbo.Admin2_543 then nothing would be returned since the join is based on personid being null in the joning table.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 1, 2010 at 12:58 pm
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 1:28 pm
the issue is if there is at least one record in the select inside the where that you are "not in"ing to then the whole result set returns nothing. So in this instance because there is one record in the successfulcals table then the whole result set returns nothing...thanks guys
--Query 1
select tt.*
from TempBaby.dbo.Admin2_543 tt (nolock)
WHERE
tt.PersonId not IN
(
SELECT PersonId
FROM TempBaby.dbo.SuccessCals (nolock)
)
July 2, 2010 at 11:47 am
This looks like it could be a case of mistaken identity....
Please try this:
select tt.*
from TempBaby.dbo.Admin2_543 tt (nolock)
WHERE
tt.PersonId not IN
(
SELECT SC.PersonId
FROM TempBaby.dbo.SuccessCals AS SC (nolock)
)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply