Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yperiwn
Starting Member
1 Post |
 Posted - 2012-02-07 : 00:46:15
|
| Hi there. First off, I have the following tables:hospital: hid hname citypatient: pid pname byear bplacevisit: pid hid date diagnosisiddisease: did dname check_period_months riskWhat I need is to trace the disease infections. I consider that a disease X is transmitted when a patient is diagnosed with X in a hospital and in the same hospital there is another patient who is diagnosed with X later. I am using this sql statement below, but I am getting the wrong results. Could you please help me out? Thanks in advance.SELECT DISTINCT(p1.pname), p2.pname, h1.hname, v1.date, h3.hname, v3.date FROM patient p1, patient p2, visit v1, visit v2, visit v3, hospital h1, hospital h2, hospital h3, disease d1, disease d2, disease d3 WHERE p1.pid=v1.pid AND v1.hid=h1.hid AND p1.pid!=p2.pid AND p2.pid=v2.pid AND v2.hid=h1.hid AND v1.date=v2.date AND v1.diagnosisid=d1.did AND v2.diagnosisid=d2.did AND d1.did=d2.did AND d1.dname="some_disease" AND p2.pid=v3.pid AND v3.hid=h3.hid AND v3.date>v2.date AND v3.diagnosisid=d3.did AND d3.did=d1.did |
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2012-02-07 : 11:28:29
|
| what results do you get? and what results do you expect? |
 |
|
|
|
|
|
|
|
tracing disease infections