Re: a question for you SQL wizards!

> Jim Pistrang wrote:
> >
> > Dear SQL wizards,
> >
> > Background:
> >
> > Consider the following tables...
> > DOCTOR unique key DOC_ID
> > PATIENT unique key PAT_ID
> > DOCPAT unique key DOC_ID + PAT_ID
> >
> > A doctor can have 0 or 1 or many patients, and a patient can have 1 or
> > many doctors. Therefore DOCPAT is a 'junction table', used to establish a
> > many-to-many relationship between doctors & patients.
> >
> > Here's the problem/question. I need to build a list of patient info,
> > where each line in the list represents a single patient. Included in the
> > list, I need to know if ANY of the doctors that this patient is connected
> > to have any OTHER patients. It's not critical that I know how many
> > doctors have other patients, or which doctors, just IF.
> >
> > Obviously, I could loop thru the entire list of patients & do a SQL call
> > for each one, but I'd REALLY like to make just 1 SQL call to get all the
> > info I need. I also thought of making a VIEW that has DOC_ID and a
> > count(*) of all patients connected to the DOC, but that still doesn't
> > quite give me what I want.
> >

Here's one that returns patient data plus a "Y" if the doctor has other patients from the table and "N" if he doesn't (I think, I haven't tried it):

How about this:

SELECT PAT_ID, other_patient_columns, 'Y' FROM PATIENT A   WHERE EXISTS (SELECT B.PAT_ID FROM PATDOC B WHERE B.PAT_ID = A.PAT_ID   AND EXISTS (SELECT C.PAT_ID FROM PATDOC C WHERE C.PAT_ID <> B.PAT_ID   AND C.DOC_ID = B.DOC_ID))
UNION
SELECT PAT_ID, other_patient_columns, 'N' FROM PATIENT A   WHERE NOT EXISTS (SELECT B.PAT_ID FROM PATDOC B WHERE B.PAT_ID = A.PAT_ID   AND EXISTS (SELECT C.PAT_ID FROM PATDOC C WHERE C.PAT_ID <> B.PAT_ID   AND C.DOC_ID = B.DOC_ID)); Whew! Does that work???

At least it's one query!

Larry

• Larry E. Snyder * *
• Monsanto Company * CLEVER QUOTE OR PHRASE GOES HERE *
• St. Louis, MO * lesnyd_at_monsanto.com *

  ---