Any SELECT Statement Gurus Out There?

Hi all,

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

Any MySQL book recommendations for newbie?

I just bought the book, Build Your Own Database Driven Website, by
Kevin Yank, and I'll start working on a database soon. Any other
books, etc., you can think of that a know-nothing newbie should read?
Is the book above a good one?

Thanks!
Steve HThis is a Microsoft SQL Server group so if you are looking for
recommendations on MySQL books you will probably get more help elsewhere.

Some book lists and other resources for SQLServer (not MySQL):

http://vyaskn.tripod.com/sqlbooks.htm
http://www.aspfaq.com/2423

--
David Portas
SQL Server MVP
--

Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo