Re: A Question about a query on three tables

This sql query

select distinct p.pid, p.name,

     kt.kommnr tel, yt.code code_tel,
     kf.kommnr fax, yf.code codefax
from partner p,
     komm kt, komtyp yt,
     komm kf, komtyp yf
where p.pid=4711 and
      p.pid=kt.parid and
      kt.ktypid=yt.ktid and
      yt.code='TEL' and
      p.pid=kf.parid and
      kf.ktypid=yf.ktid and
      yf.code='FAX'

give me that result

      PID NAME TEL CODE_TEL FAX CODEFAX --------- ---------- ---------- ---------- ---------- ----------

     4711 SOMEBODY 0221/3091 TEL 0221/4102 FAX Markus Schwabe escribió en mensaje <8g0es4$khs$1_at_news4.muc.eurocyber.net>...
>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