Re: a question for you SQL wizards!

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

If you are on Oracle 7, one way is to write a stored function that will check if the condition is true.    

  FUNCTION has_doc_with_other_patients (patient_id_in number)

                             RETURN VARCHAR2 IS flag_yn VARCHAR2(1);
    BEGIN
      flag_yn := 'N';
      SELECT MAX('Y') INTO flag_yn
      FROM DOCPAT
      WHERE PAT_ID != patient_id_in;
        RETURN flag_yn;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          flag_yn := 'N';
          RETURN flag_yn;

Then call the function in your select statment.

Select PAT_ID,..., has_doc_with_other_patients(PAT_ID) from PATIENT;

-- 
Scott Harvester

phone: (619) 658-4713            email: sharvester_at_qualcomm.com