A Question about a query on three tables

From: Markus Schwabe <mschwabe_at_gscout.de>
Date: 2000/05/18
Message-ID: <8g0es4$khs$1@news4.muc.eurocyber.net>#1/1

Hi,

I have three tables:

1. Table PARTNER with P_ID and NAME
2. Table KOMM with KO_ID, PAR_ID, KTYP_ID and KOMM_NR
3. Table KOMTYP with KT_ID and CODE.

Relation:
PARTNER is One-to-Many to KOMM (P_ID -> PAR_ID) KOMM is One-to-Many to KOMTYP (KTYP_ID -> KT_ID)

Table PARTNER has one row:

1. P_ID = 4711, NAME = 'SOMEBODY';

Table KOMM has two rows:

1. KO_ID = 1, PAR_ID = 4711, KTYP_ID = 8, KOMM_NR = '0221/3091'
2. KO_ID = 2, PAR_ID = 4711, KTYP_ID = 9, KOMM_NR = '0221/4102'

Table KOMTYP has two rows:

1. KT_ID = 8, CODE = 'TEL'
2. KT_ID = 9, CODE = 'FAX'.

Now I want to have a query wich gives me ONE row with both Telephone- and Faxnumber like:

P_ID NAME TEL CODE_TEL FAX CODE_FAX

---

---

4711     SOMEBODY    0221/3091    TEL                  0221/4102       FAX

I've tried it with several outer-joins, but it does not work. Either I get four rows like a cartesian product (?) :

SELECT

       p.id,
       p.name,
       kt.Komm_nr tel,
       yt.Code code_tel,
       kf.Komm_nr fax,
       yf.Code code_fax,
FROM
       Partner p,
       Komm kt, Komtyp yt,
       Komm kf, Komtyp yf
WHERE
          p.id = 4411

AND p.id = kt.par_id (+) AND kt.ktyp_id = yt.id (+) AND yt.code (+) = 'TEL' AND p.id = kf.par_id (+) AND kf.ktyp_id = yf.id (+) AND yf.code (+) = 'FAX'; P_ID NAME TEL CODE_TEL FAX CODE_FAX

---

---

4711     SOMEBODY    0221/3091    TEL                  0221/3091
4711     SOMEBODY    0221/3091    TEL                  0221/4102       FAX
4711     SOMEBODY    0221/4102                             0221/3091
4711     SOMEBODY    0221/4102                             0221/4102

FAX (you see: only rowid 2 has the right result)

or (if I delete some outer joins) I get one row (horay!), but when I delete one
row in table KOMM, my query returns no rows (but I WANT to have one row with then a NULL in TEL- or in FAX-Column).

With the exception of yt.code ('TEL') and yf.code ('FAX') I want no hartcoded
statements like:
...

WHERE kt.ktyp_id = 8
AND kf.ktyp_id = 9
...

I don't want to ignore table KOMTYP or any other table.

Is it possible? Do you have the right query for me?

I hope that I have explained you my problem clearly and did not forget any more information.

Thank you very much for your brainstorm. Markus Schwabe Received on Thu May 18 2000 - 00:00:00 CDT