Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> A Question about a query on three tables
Hi,
I have three tables:
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:
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
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/4102FAX (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