April 3, 2013 at 3:42 pm
2 Tables.
Table C
VISIT#, SSN, FACILITY, DOCUMENT NAME
Table H
VISIT#, SSN, DATE
Visit# | SSN | DOCNAME | DATE
1 | 555 | Doc A | 01.01.01
1 | 555 | Doc B | 01.01.01
1 | 555 | Doc C | 01.01.01
2 | 777 | Doc A | 01.01.01
2 | 777 | Doc D | 01.01.01
2 | 777 | Doc C | 01.01.01
3 | 999 | Doc A | 01.01.01
3 | 999 | Doc B | 01.01.01
3 | 999 | Doc C | 01.01.01
4 | 555 | Doc A | 01.02.01
4 | 555 | Doc D | 01.02.01
4 | 555 | Doc C | 01.02.01
5 | 777 | Doc A | 01.03.01
5 | 777 | Doc D | 01.03.01
5 | 777 | Doc C | 01.03.01
I want to figure out which visits are missing Doc B...
So Visit 5 | SSN 777 | 01.03.01 and Visit 4 | SSN 555 | 01.02.01 and Visit 2 | SSN 777 | 01.01.01
I am doing my join like so...
table C Inner Join table H on C.visit# = H.visit#
Thanks for the help!
April 3, 2013 at 3:58 pm
Something like this:
select *
from
TableH h
where
exists(select 1 from TableC c where c.[visit#] = h.[visit#] and c.[document name] = 'Doc B');
April 3, 2013 at 6:55 pm
Lynn,
Wouldn't that give me Visits with or that have doc name B? I'm looking for the ones that do not have a specific document...that are missing a document. You could have it right, I have not run it yet. I would just think I would want it to say not exists or not in vs exists. Am I wrong?
April 3, 2013 at 9:26 pm
Vertigo44 (4/3/2013)
Lynn,Wouldn't that give me Visits with or that have doc name B? I'm looking for the ones that do not have a specific document...that are missing a document. You could have it right, I have not run it yet. I would just think I would want it to say not exists or not in vs exists. Am I wrong?
Yes, you are right. Change the EXISTS to NOT EXISTS and see if that works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply