|
|
Posted by Eric Robinson on 10/18/84 11:26
I was offline for a few hours and I see that I have some very throughtful
replies. I will take a closer look at them. In the meantime, based on Stu's
initial suggestion, I came up with the following, which SEEMS to work:
For simplicity's sake, I lied in my original message about the true names of
the tables, so the following query looks slightly different because it
contains real table names. (In the future I'll take Erland's suggestion and
post CREATE TABLE scripts instead.)
SELECT s.Specialty, p.LastName, p.FirstName, p.Degree, g.GroupName,
f.PhysAddr1, f.PhysCity, f.PhysAddr2, f.PhysState, f.Phone1
FROM tblProviders p JOIN tblBindProviderGroup p2g on p.ProviderID =
p2g.ProviderID
JOIN tblGroups g on g.GroupID=p2g.GroupID
JOIN tblFacilities f on f.GroupID=p2g.GroupID
JOIN tblBindProviderSpecialty p2s on p2s.ProviderID=p.ProviderID
JOIN tblSpecialties s on s.SpecialtyID=p2s.SpecialtyID
WHERE f.PhysCity='Reno'
ORDER BY p.LastName
I've run this against the actual data and it SEEMS to return correct
results.
I'm guessing that some of the other approaches people have posted are better
in ways I have not yet thought of.
--Eric
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96CFEF79E2BE1Yazorman@127.0.0.1...
> Eric Robinson (eric @ pmcipa..{com}) writes:
>> These tables are part of our medical database. For example, a person
>> seeking healthcare may want to know the names and contact information
>> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
>> isn't.
>>
>> A medical group can have many doctors.
>> A doctor may be a member of more than one medical group.
>> A doctor or group can have multiple specialties.
>> A group can have multiple facilities (physical locations).
>>
>> So the tables look like this...
>
> It is always preferable to post CREATE TABLE scripts for the table.
> That and test data in INSERT statemants, and the desired result of
> the test data. That will give you a tested solution.
>
> So this is an untested solution:
>
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno')
> AND EXISTS (SELECT *
> FROM Doc2Specialiity DS
> JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
> WHERE DS.DoctorID = S.DoctorID
> AND S.SpecialityDesc = 'Cardiology')
> UNION
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
> JOIN Specialtities S ON GS.SpecialityID =
> S.SpecialityID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno'
> AND S.SpecialityDesc = 'Cardiology')
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
Navigation:
[Reply to this message]
|