Tracing disease infections

Dev Shed Forums Sponsor:

February 6th, 2012, 11:53 PM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

Tracing disease infections

Hi there. First off, I have the following tables:

hospital:
hid
hname
city

patient:
pid
pname
byear
bplace

visit:
pid
hid
date
diagnosisid

disease:
did
dname
check_period_months
risk

What 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

February 7th, 2012, 06:08 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

two suggestions:

first, never write this --

Code:

SELECT DISTINCT(p1.pname), ...

that makes it look like DISTINCT is a function, and it's not

DISTINCT applies to all columns in the SELECT cluase, not just the first one... and the first one shouldn't be in parentheses

second, please use explicit JOIN syntax

the way you have it, all the join conditions are lumped together in the WHERE clause in one huge mishmash

with explicit JOIN syntax, it's a lot easier to see which columns are being used to join which tables

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 7th, 2012, 08:07 AM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

Ok, but how exactly am I going to express the condition with JOIN?
EDIT: Do you mean something like this?

SELECT DISTINCT p1.pname, p2.pname, h1.hname, v1.date, h3.hname, v3.date FROM patient p1 JOIN visit v1 JOIN hospital h1 JOIN disease d1, patient p2 JOIN visit v2 JOIN hospital h2 JOIN disease d2 JOIN visit v3 JOIN hospital h3 JOIN disease d3 WHERE p1.pid!=p2.pid AND v1.date=v2.date AND d1.did=d2.did AND d1.dname='some_disease' AND v3.date>v2.date AND d3.did=d1.did

I tried it and hangs. :\

February 7th, 2012, 08:39 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

Quote:

Originally Posted by vril

Ok, but how exactly am I going to express the condition with JOIN?

like this --

Code:

SELECT DISTINCT
       p1.pname
     , p2.pname
     , h1.hname
     , v1.date
     , h3.hname
     , v3.date 
  FROM patient p1
INNER
  JOIN visit v1
    ON v1.pid = p1.pid 
INNER
  JOIN hospital h1
    ON h1.hid = v1.hid    
INNER
  JOIN disease d1    
    ON d1.did = v1.diagnosisid
   AND d1.dname = "some_disease"
INNER
  JOIN patient p2
    ON p2.pid <> p1.pid    
INNER
  JOIN visit v2
    ON v2.pid = p2.pid
   AND v2.hid = h1.hid
   AND v2.date = v1.date
INNER
  JOIN hospital h2
    ON ??????????????????
INNER
  JOIN disease d2  
    ON d2.did = v2.diagnosisid 
   AND d2.did = d1.did
INNER
  JOIN visit v3
    ON v3.pid = p2.pid
   AND v3.date > v2.date
INNER
  JOIN hospital h3
    ON h3.hid = v3.hid
INNER
  JOIN disease d3 
    ON d3.did = v3.diagnosisid
   AND d3.did = d1.did

notice that the joins are written in a particular sequence, and also that each join has ON clause conditions that connect the table being joined using column(s) of table(s) previously mentioned, i.e. higher up in the FROM clause

before you do anything else, i would like you to agree that this is ~way~ more easily comprehended than the mishmash of the WHERE clause

in fact, this style has made it obvious that you've forgotten to join one of the tables properly

go ahead, look through your original query, and see if you can easily spot the missing join condition there

February 7th, 2012, 09:24 AM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

Since the way tables are connected is obvious (common fields), I was thinking that the 'ON' can be ommited. Yes, indeed it 's more apprehensible so, but logic remains the same, doesn't it? Also, my mistake, I needed 2 instances of hospital instead of 3 in fact. Anyway, the problem is that I am still getting the wrong results:

pname from | pname to | infection hname | infection date | diagnosis hname | diagnosis date
Archimedes Picasso | Ryan Picasso | Interfaith | 1/7/60 | New York | 3/2/48

Which doesn't make any sense, because it presents diagnosis date prior to infection date.

February 7th, 2012, 09:46 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

Quote:

Originally Posted by vril

Anyway, the problem is that I am still getting the wrong results:

may i see the query please?

February 7th, 2012, 09:54 AM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

It 's not much different from the one you showed to me:

SELECT DISTINCT p1.pname, p2.pname, h1.hname, v1.date, h2.hname, v3.date FROM patient p1 INNER JOIN visit v1 ON v1.pid = p1.pid INNER JOIN hospital h1 ON h1.hid = v1.hid INNER JOIN disease d1 ON d1.did = v1.diagnosisid AND d1.dname = 'some_disease' INNER JOIN patient p2 ON p2.pid <> p1.pid INNER JOIN visit v2 ON v2.pid = p2.pid AND v2.hid = h1.hid AND
v2.date = v1.date INNER JOIN disease d2 ON d2.did = v2.diagnosisid AND d2.did = d1.did INNER JOIN visit v3 ON v3.pid = p2.pid AND v3.date > v2.date INNER JOIN hospital h2 ON h2.hid = v3.hid INNER JOIN disease d3 ON d3.did = v3.diagnosisid AND d3.did = d1.did

February 7th, 2012, 10:25 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

Quote:

Originally Posted by vril

It 's not much different from the one you showed to me:

sheeeit, man, i can't read that

start with something simple

Code:

SELECT p1.pname     
     , v1.date
     , h1.hname
     , d1.dname 
  FROM patient p1 
INNER
  JOIN visit v1
    ON v1.pid = p1.pid 
INNER
  JOIN hospital h1
    ON h1.hid = v1.hid 
INNER
  JOIN disease d1
    ON d1.did = v1.diagnosisid 
   AND d1.dname = 'some_disease'

presumably this returns correct results, as far as they go, right?

so now read with me through the subsequent joins

Code:

INNER
  JOIN patient p2
    ON p2.pid <> p1.pid 
INNER
  JOIN visit v2
    ON v2.pid = p2.pid
   AND v2.hid = h1.hid 
   AND v2.date = v1.date 
INNER
  JOIN disease d2
    ON d2.did = v2.diagnosisid 
   AND d2.did = d1.did

different patient, visited same hospital, same date, same disease

so far so good, yes? (confirm this by adding p2.pname, v2.date, h2.hname and d2.dname to the SELECT clause)

if that also checks out, then the problem lies in the remaining joins

February 7th, 2012, 10:53 AM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

Ok, it gives me the same result I had posted above (Archimedes Picasso and Ryan Picasso). For your convenience, here is that query in the legible way:

SELECT DISTINCT p1.pname, p2.pname, h1.hname, v1.date, h2.hname, v3.date
FROM patient p1 INNER JOIN visit v1
ON v1.pid = p1.pid

INNER JOIN hospital h1
ON h1.hid = v1.hid

INNER JOIN disease d1
ON d1.did = v1.diagnosisid
AND d1.dname = 'some_disease'

INNER JOIN patient p2
ON p2.pid <> p1.pid

INNER JOIN visit v2
ON v2.pid = p2.pid
AND v2.hid = h1.hid
AND v2.date = v1.date

INNER JOIN disease d2
ON d2.did = v2.diagnosisid
AND d2.did = d1.did

INNER JOIN visit v3
ON v3.pid = p2.pid
AND v3.date > v2.date

INNER JOIN hospital h2
ON h2.hid = v3.hid

INNER JOIN disease d3
ON d3.did = v3.diagnosisid
AND d3.did = d1.did

#10

February 7th, 2012, 11:19 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

Quote:

Originally Posted by vril

Ok, it gives me the same result I had posted above (Archimedes Picasso and Ryan Picasso).

the query looks okay, are you sure your data is correct?

are the date columns actual DATE columns? or are they VARCHAR?

because as VARCHARs, '1/7/60' definitely comes before '3/2/48'

#11

February 7th, 2012, 11:35 AM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

Quote:

Originally Posted by r937

the query looks okay, are you sure your data is correct?

are the date columns actual DATE columns? or are they VARCHAR?

because as VARCHARs, '1/7/60' definitely comes before '3/2/48'

My data is correct beyond doubt.
To be precise, they are varchar. How can I compare them under those circumstances?

#12

February 7th, 2012, 12:02 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140

Quote:

Originally Posted by vril

How can I compare them under those circumstances?

try the CONVERT function

CONVERT(DATE,v1.date,101)

101 is mm/dd/yyyy, 103 is dd/mm/yyyy

#13

February 7th, 2012, 03:54 PM

vril

Registered User

Join Date: Aug 2009

Posts: 21

Time spent in forums: 3 h 3 m 18 sec
Reputation Power: 0

I substituted v3.date>v2.date with

Code:

CONVERT(DATE, v3.date, 111) > CONVERT(DATE, v2.date, 111)

, as dates have the form of "yy/mm/dd" and it tells me that there is an error in my SQL syntax near 'v3.date, 111) > CONVERT(DATE, v2.date, 111)'.
???