Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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. :\
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by vril
    Anyway, the problem is that I am still getting the wrong results:
    may i see the query please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0
    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?
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep 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)'.
    ???
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by vril
    ...as dates have the form of "yy/mm/dd"
    o, rly?

    1/7/60 and 3/2/48 would seem to contradict that



    as for the error, perhaps you could try again with DATETIME instead of DATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0
    Originally Posted by r937
    o, rly?

    1/7/60 and 3/2/48 would seem to contradict that



    as for the error, perhaps you could try again with DATETIME instead of DATE
    Yes, I updated the dates meanwhile.
    DATETIME didn't work either. :\
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo