hi Markus,
you can declare for help some views
// for tel
create or replace view hlp_tel
as select
a.par_id
,a.komm_nr
,b.code
from komm a, komtyp b
where ktyp_id = b.kt_id
and b.kt_id = 8;
// for fax
create or replace view hlp_fax
as select
a.par_id
,a.komm_nr
,b.code
from komm a, komtyp b
where ktyp_id = b.kt_id
and b.kt_id = 9;
// for result
create or replace view v_tel_fax
as select
c.p_id p_id
,c.name name
,a.komm_nr FAX
,a.code Fax_Nr
,b.komm_nr TEL
,b.code Tel_Nr
from hlp_fax a, hlp_tel b, partner c
where c.p_id = a.par_id(+)
and c.p_id = b.par_id(+);
SQL>select * from v_tel_fax;
in 0racle 8i (oracle 7.3 and above ?) you can mix it all (its not very
clear)
create or replace view v_tel_fax as
select
c.p_id p_id
,c.name name
,a.komm_nr FAX
,a.code Fax_Nr
,b.komm_nr TEL
,b.code Tel_Nr
from
( select
a.par_id
,a.komm_nr
,b.code
from komm a, komtyp b
where ktyp_id = b.kt_id
and b.kt_id = 8) a
, (select
a.par_id
,a.komm_nr
,b.code
from komm a, komtyp b
where ktyp_id = b.kt_id
and b.kt_id = 9) b
, partner c
where c.p_id = a.par_id(+)
and c.p_id = b.par_id(+);
;
hope it works
Matthias
Markus Schwabe <mschwabe_at_gscout.de> schrieb in im Newsbeitrag:
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
