SQL Crew

Discussion in 'OT Technology' started by Seeders, Feb 3, 2009.

  1. Seeders

    Seeders OT Supporter

    Joined:
    Jul 10, 2007
    Messages:
    3,750
    Likes Received:
    0
    I have three tables -

    Patient (pid, pname, age)
    Appointment (pid, did, time)
    Doctor (did, dname, level)

    Find the pids of patients who have made appointments with every doctor of level 1.

    Im thinking i have to do a SELECT of all doctors with level = 1, and then intersect/difference it with something else but i cant figure it out. :noes:
     
  2. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    Code:
    select p.pid from patient p inner join appointment a on p.pid = a.pid inner join doctor d on d.did = a.did where d.level = 1
     
  3. Seeders

    Seeders OT Supporter

    Joined:
    Jul 10, 2007
    Messages:
    3,750
    Likes Received:
    0
    doesn't that just get a list of all appointments made by patients with doctor level 1?

    i need a list of patients who have personally made appointments with each and every doctor of level 1.
     
  4. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    nope because of this part
    Code:
    select p.pid
    that selects pids only.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    No, what he's saying is that all that returns is all the patient appointments where the doctor level is 1. Sure it only returns the pids, but a) it returns the same pid multiple times which isn't that big of a deal if you just add a group by, but b) it doesn't show which patients have used ALL of the level 1 docs which is the bigger problem.

    You could do a group by query to select all the doctor level 1's each patient has had and then sum the did's. If the sum of the did's add up for each patient to the sum of all level one dids, then they've seen every level 1 doc.
     
  6. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Oh lol...yeah good point. Guess I should have slowed down in my reading. :)
     
  7. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I have a pretty serious concussion, so bare with me here but...

    I believe you want to GROUP BY pid and COUNT(DISTINCT/UNIQUE(did)) vs. global count of did, and of course, where level = 1.
     
  8. critter783

    critter783 OT Supporter

    Joined:
    Jul 15, 2005
    Messages:
    1,785
    Likes Received:
    0
    That's the path I was taking yesterday afternoon when I was trying to come up with a solution. Basically my strategy was to get the count of level one doctors, then select the pids from apt where the count(distinct apt.did) was equal to the count of level one doctors.
     
  9. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    ya, i think that's probably the best solution. i was going to come up with something but then i already noticed he had his question answered in the main forum :o
     
  10. Seeders

    Seeders OT Supporter

    Joined:
    Jul 10, 2007
    Messages:
    3,750
    Likes Received:
    0
    Well i got home and read my book and it gives this example:

    Find the names of sailors who have reserved all boats

    Code:
    SELECT S.sname
    FROM Sailors S
    WHERE NOT EXISTS(( SELECT B.bid
                        FROM Boats B
                        WHERE NOT EXISTS ( SELECT R.bid
                                           FROM Reserves R
                                           WHERE R.bid = B.bid 
                                               AND R.sid = S.sid ))
    
    For each sailor S, we check that there is no boat that has not been reserved by this sailor.

    so i converted this example to mine and did:

    Code:
    SELECT P.pid
    FROM Patients P
    WHERE NOT EXISTS(( SELECT D.did
                        FROM Doctors D
                        WHERE NOT EXISTS ( SELECT A.bid
                                           FROM Appointments A
                                           WHERE A.did = D.did 
                                               AND A.pid = P.pid 
                                               AND D.level = 1))
    
     

Share This Page