Posted by Eric Robinson on 12/06/62 11:26

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.googlegroups.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
>

[Back to original message]