Posted by Hugo Kornelis on 11/10/47 11:26

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)

[Back to original message]