a question for you SQL wizards!
Date: 1996/04/14
Message-ID: <pistrang-1404962222250001_at_iplink048.crocker.com>#1/1
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...
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
DOCTOR unique key DOC_ID
PATIENT unique key PAT_ID
DOCPAT unique key DOC_ID + PAT_ID
Jim Pistrang
JP Computer Resources
--
Jim Pistrang
JP Computer Resources
http://www.crocker.com/~pistrang
Received on Sun Apr 14 1996 - 00:00:00 CEST