March 23, 2011 at 6:18 pm
I cant give ddl becuase it is in #temps. But what I need to do get all rows that exists in the second table but not in the first.
For example the gmpi column (which is our internal number patient number) is what I join the first table to the second. Then when I use the not exists below, it will truly give me records that only exist in the second table that do not exists in the first, but it leaves some out????
I am mind boggled.
An easy way to look at it is if there are 10 values total.
7 of them are in the first table and 3 are only in the second. The where exists will only get 2 or the 3 from second. Under what conditions would this happen.
Is there another approach to 'where not exist' (in first table) that can get ALL the rows out?
I know the below code is not very helpful but
I made 2 simple tables with 10 records a peice and the same 'where not exist' worked as it should.
Has anybody had it pull rows that do not exist but leave some?
Again if there is another approach to get only the rows in a second table where there is no matching row in the first, I would be thrilled to try it. I was thinking well maybe 1 of the three that it is not pulling is in the first and the second table. But there not, all 3 are only in the second table, but my 'where not exists' only gets 2 of the 3?
drop table ##ValitdationData
select
ve.DM_AuditNurseID
,ve.gmpi
,vd.VD_ICD9 'ICD9'
,ve.DM_ProviderID
--,'' as 'AE_AuditDate'--simply needs to make matching columns for union down the line - it servrs no other purpose
,vd.VD_Date 'DOS'
,'' as DM_AuditEncounterID --*needed for Union Join, not actually a column in the Validation tables
,'' as 'Audit_Action'
,lu.VS_Abbbr 'Confirm_Status'
into ##ValitdationData
from dbo.tblDM_ValidationEncounter ve
join dbo.tblDM_ValidationDiag vd
on ve.DM_ValidationEncounterID = vd.DM_ValidationEncounterID
join dbo.tblDM_ValidationStatusLookup lu
on lu.DM_ValidationStatusID = vd.DM_ValidationStatusID --will return correct gmpi
where year(vd.VD_Date) between 2007 and 2011
--select * from ##ValitdationData where gmpi = 53584 -- and icd9 = 780.39
drop table ##InValOnly
select v.*
into ##InValOnly
from ##ValitdationData V
join ##temp3 audit
on v.gmpi = audit.gmpi--66
GO
drop table ##Icd9sOnlyInVal
select *
into ##Icd9sOnlyInVal
from ##ValitdationData where not exists
(SELECT * FROM ##temp3 WHERE ##temp3.icd9 = ##ValitdationData.ICD9)
-------------------------------------------------
select * from ##Icd9sOnlyInVal where gmpi = 53584 and icd9 = 170.90
select * from ##Icd9sOnlyInVal where gmpi = 53584 and icd9 = 780.39
Thanks in advance!
March 24, 2011 at 11:00 am
I think your logic for not exists is not quite what you are looking for.
select *
into ##Icd9sOnlyInVal
from ##ValitdationData where not exists
(SELECT * FROM ##temp3 WHERE ##temp3.icd9 = ##ValitdationData.ICD9)
This will insert all the records from ##ValitdationData if there are no records in ##temp3 WHERE ##temp3.icd9 = ##ValitdationData.ICD9
it is not really clear what you are trying but i think you want to insert where ##temp3.icd9 has no match to ##ValitdationData.ICD9???
see if this is what you are looking for
select *
into ##Icd9sOnlyInVal
from ##ValitdationData
left join ##temp3 on ##temp3.icd9 = ##ValitdationData.ICD9
where ##temp3.icd9 is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2011 at 4:05 am
just try simple change in your last line just reverse the condition in exists clause and change the condition in subquery
means
replace
Not exists
with ]
Exists
and where condition not equal
where exists
(SELECT * FROM ##temp3 WHERE ##temp3.icd9 != ##ValitdationData.ICD9)
let me know if it works then will tel you the reason.... 🙂
as i faced the same issue once ..
March 29, 2011 at 11:59 am
I already finished that assignment before I saw the replies. Thank you very much. I did read and understand the suggestions and will come back to it on my next where not exists or where exists.
Super thanks,
Adam
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply