I'm having real trouble wrapping my newbie brain around this problem. Can
someone please tell me the most efficient (or any!) way to write a SELECT
statement to return a set of rows from 5 tables:
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...
Table: Doctors
-----
DoctorName
DoctorID
Table: Groups
-----
GroupName
GroupID
Table: Docs2Groups (provides many-to-many relationship between Doctors and
Groups)
-------
DoctorID
GroupID
Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID
Facilities
---
FacilityName (provides one-to-many relationship between Groups and
Facilities)
FacilityID
GroupID
Address
City
State
Zip
Phone
E-mail
Any help would be GREATLY appreciated.
--Eric Robinsonthis table confuses me:
Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID
Are you saying that a doctor can only specialize in something within
the context of a certain group? In other words Dr.Smith is a pediatric
oncologist, but can only work in pediatrics for Group A and oncology
for group B? Does that happen?
It would be easier if a Group was a collection of doctors who all had
specialties.
SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d.DoctorID = s.DoctorID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'
But, if a Doctor can only specialize in something in the context of a
group, then you could do this:
SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d2g.DoctorID = s.DoctorID
AND d2g.GroupID = s.GroupID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'
Untested.
HTH,
Stu|||Stu,
Each doctor has his or her own primary specialty and 0 or more additional
specialties. These specialties are asociated with the doctor, not the group.
A group's specialty is a function of its participating doctors. If all the
docs for Group A are cardiologists, then Group A is considered a cardiology
group. If the docs are of different specialties, then the group is
considered "multispecialty." In that sense, a group is, as you say, a
collection of doctors.
HOWEVER, there are unusual cases where the group consists of docs with
different specialties, but it still wants to to be known (for the purposes
of our directory) as one certain kind of group. In these cases, the group
itself gets an "overriding" specialty associated with it to keep from being
listed as "multispecialty."
Therefore the Specialties table does double-duty, but there are only a few
records with GroupID <> 0.
Does that clear things up?
--Eric
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Oops, I mistated the Specialties table. It is actually three tables:
Specialty
---
SpecialtyID
SpecialtyDesc
Doc2Specialty
------
SpecialtyID
DoctorID
Group2Specialty
------
SpecialtyID
GroupID
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||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/techin.../2000/books.asp|||On Mon, 12 Sep 2005 12:36:02 -0700, Eric Robinson wrote:
>Oops, I mistated the Specialties table. It is actually three tables:
(snip)
Hi Eric,
I'm glad you posted that, since I was just preparing to telll you how
you should change your design and all <grin>.
Anyway, since we now have doctors who do cardiology, groups that
specialize in cardiology and doctors that belong to groups that do
cardiology, it's not exactly clear what you want your query to return.
I'll assume you want to know what I would be interested in if my heart
starts acting funny while I'm in Reno - I'd like to know where to go
(and quick!).
The query below will return the name of the group and the details of the
location for each group with a location in Reno, NV that either has
cardiology as group specialization, or hosts at least one doctor who
specializes in cardiology.
SELECT g.GroupName, f.FacilityName, f.Address, f.Phone
FROM Groups AS g
INNER JOIN Facilities AS f
ON f.GroupID = g.GroupID
LEFT JOIN Group2Specialties AS g2s
ON g2s.GroupID = g.GroupID
CROSS JOIN (SELECT SpecialtyID
FROM Specialties
WHERE SpecialtyDesc = 'Cardiology') AS s(SpecialtyID)
WHERE f.City = 'Reno'
AND f.State = 'NV'
AND
( g2s.SpecialtyID = s.SpecialtyID
OR EXISTS
(SELECT *
FROM Docs2Groups AS d2g
INNER JOIN Doc2Specialties AS d2s
ON d2s.DoctorID = d2g.DoctorID
WHERE d2g.GroupID = g.GroupID
AND d2s.SpecialtyID = s.SpecialtyID))
(untested - see www.aspfaq.com/5006 if you prefer tested suggestions).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||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/techin.../2000/books.asp
