Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Please! Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 3:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:16 AM
Points: 219, Visits: 724
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!
Post #1438603
Posted Wednesday, April 3, 2013 3:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 20,734, Visits: 32,498
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');





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438607
Posted Wednesday, April 3, 2013 6:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:16 AM
Points: 219, Visits: 724
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?
Post #1438630
Posted Wednesday, April 3, 2013 9:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 20,734, Visits: 32,498
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse