June 2, 2014 at 5:45 pm
The is query is not working as expected (or at least as I expect).
There is one SSN in the VEStaging that is not in the EHR table.
But for some reason this query doesn't return anything.
It should be listing any record in the VEStaging table that does not have an record with the same SSN. But it returns nothing.
SELECT *
FROM VEStaging ves
WHERE ves.ssn NOT IN ( SELECT ehr.ssn
FROM EHR ehr )
What am I missing?
Thanks,
Tom
June 2, 2014 at 6:40 pm
Without seeing the data? Nearly impossible to troubleshoot directly.
What's the rowcount in VES?
What's the rowcount in EHR?
What's the DISTINCT SSN Count in both?
When you join the tables (instead of NOT IN, JOIN ON), what's your rowcount?
Do you get the same results if you LEFT JOIN ON and then look for only items with ehr.ssn IS NULL?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2014 at 6:49 pm
To test this, I put together this test which does work fine. If you take out the where clause, I still get one row back (ssn = 2).
I took the same query and just replaced the new tables (and actual data).
drop table #temp1
drop table #temp2
Create Table #temp1
(
Name varchar(10),
SSN varchar(10)
)
CREATE table #temp2
(
eid int,
SSN varchar(10)
)
Insert #temp1(Name, SSN) values ('tom', '1')
Insert #temp1(Name, SSN) values ('larry', '2')
Insert #temp1(Name, SSN) values ('frank', '3')
Insert #temp2(eid,SSN) values ('100', '1')
Insert #temp2(eid,SSN) values ('300', '3')
SELECT *
FROM #temp1 ves
WHERE ves.ssn NOT IN ( SELECT ehr.ssn
FROM #temp2 ehr ) AND
ves.ssn = 2
June 2, 2014 at 6:57 pm
I did try the LEFT JOIN and it works correctly with the results expected.
SELECT *
FROM VEStaging ves
LEFT JOIN EHR ehr
ON ves.SSN = ehr.SSN
WHERE ehr.SSN is null
There are 12,000 records in the VEStaging table and about 6,000 in the EHR table.
In the actual table, the SSN is varchar(50) where the value is encoded. It actually looks something like '++E4Vw/zjTwwCAmN/5sOJg=='
June 2, 2014 at 7:16 pm
Figured it out.
The table in the subquery was not filtering out nulls and there were nulls in that field.
So if I changed it to:
SELECT *
FROM VEStaging ves
WHERE ves.ssn NOT IN ( SELECT ehr.ssn
FROM EHR ehr
WHERE ehr.ssn IS NOT NULL)
It works fine.
Thanks,
Tom
June 3, 2014 at 11:31 am
Glad you found it Tom, good luck. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 4, 2014 at 10:10 am
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply