# SQL Crew

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

1. ### SeedersOT Supporter

Joined:
Jul 10, 2007
Messages:
3,750
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.

2. ### Limp_BrisketNew Member

Joined:
Jan 2, 2006
Messages:
48,422
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. ### SeedersOT Supporter

Joined:
Jul 10, 2007
Messages:
3,750
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. ### CoottieBOOMER......SOONEROT Supporter

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

5. ### 5Gen_PreludeThere might not be an "I" in the word "Team", but

Joined:
Mar 14, 2000
Messages:
14,519
1
Location:
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. ### CoottieBOOMER......SOONEROT Supporter

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

7. ### PeyompNew Member

Joined:
Jan 11, 2002
Messages:
14,017
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. ### critter783OT Supporter

Joined:
Jul 15, 2005
Messages:
1,785
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_BrisketNew Member

Joined:
Jan 2, 2006
Messages:
48,422
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

10. ### SeedersOT Supporter

Joined:
Jul 10, 2007
Messages:
3,750
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))
```