Re: a question for you SQL wizards!

From: Lawrence V. Rohrer <lrohrer_at_lsquared.com>
Date: 1996/04/14
Message-ID: <3171F028.318B_at_lsquared.com>#1/1

Hi!

I'm away from a SQL engine but here goes.

select P1.PAT_ID,

	P1.[other P1 variables], 
	count(DP1.DOC_ID) 'Doctors Patient Has',
	'Docs have NO other patients!'

  from PATIENT P1, DOCPAT DP1
 where P1.PAT_ID = DP1.PAT_ID /* Patient must have 1 doctor */    and not exists (select 1 /* Docs have NO other Patients */

	  from DOCPAT DP2
	 where DP2.DOC_ID = DP1.DOC_ID
	   and DP2.PAT_ID != P1.PAT_ID)

UNION
select P1.PAT_ID,

	P1.[other P1 variables], 
	count(DP1.DOC_ID) 'Doctors Patient Has',
	'Docs have other patients!'

  from PATIENT P1, DOCPAT DP1
 where P1.PAT_ID = DP1.PAT_ID /* Patient must have 1 doctor */    and exists (select 1 /* Docs have other Patients */

	  from DOCPAT DP2
	 where DP2.DOC_ID = DP1.DOC_ID
	   and DP2.PAT_ID != P1.PAT_ID) /* Be sure not to include patient */

group by 1, 2 ... [number of variables in P1 selected] /

Assumptions:
DOCPAT.DOC_ID is not null, DOCPAT.PAT_ID is not null

There are some wierdnesses...

Jim Pistrang wrote:
>
> Dear SQL wizards,
>
> I've got a question that I need to ask in SQL, & I'm pretty sure there's a
> good way to ask it, but I'm stumped. I'm 'asking' the question from an
> OMNIS application, which is a 4GL running on a client workstation against
> an Oracle Server on an NT.
>
> 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.
>
> Can anyone help?
>
> TIA,
>
> Jim
> Jim Pistrang
> JP Computer Resources
>
> --
> Jim Pistrang
> JP Computer Resources
> http://www.crocker.com/~pistrang
 

-- 
Lawrence V. Rohrer
1500 Adams #105
Costa Mesa, California 92626

lrohrer_at_lsquared.com
http://home.earthlink.net/~lrohrer/index.html

Received on Sun Apr 14 1996 - 00:00:00 CEST