Re: LEFT OUTER JOINS
От | pawel |
---|---|
Тема | Re: LEFT OUTER JOINS |
Дата | |
Msg-id | 00Aug31.100120cest.115202@fullmoon.ensi.net обсуждение исходный текст |
Ответ на | LEFT OUTER JOINS (Hoosain Madhi <MADHIH@vodacom.co.za>) |
Список | pgsql-general |
We use functions to solve this problem. If you want to use "outer join" by column ltg you can create function CREATE FUNCTION outer_join_by_id_for_class_a(int) RETURNS class_a AS ' SELECT * FROM class_a WHERE id = $1; ' LANGUAGE 'sql'; Suppose you want to read field XXX from class_a, "outer joined" by class_b.id_a = class_a.id Then you can use it this way: SELECT <some_fields>, xxx(outer_join_by_id_for_class_a(id_a)) FROM class_b WHERE .... It works fine for us, but probably there are more effitient solutions. Regards Pawel Hoosain Madhi wrote: > > I would like to perform an outer join. I know that Postgres does not support > Outer Joins and the work around is to use a combination of UNION ALL and SUB > Queries. > > In particular I am struggling with the following scenario. > There are three tables tbl_ltg, tbl_tgrp, tbl_c7links. > > tbl_tgrp references tbl_ltg with attributes (node,ltg) > tbl_c7links references tbl_ltg with attributes (node,ltg) > > I have come up with the following SQL, but as you guessed, does not work. > The fact that I request > c.c7link in the first part of the query, it returns all c7links from > tbl_c7links. Ideally I should set this to NULL and then request c.c7links in > the second part of the query (line9). However the parser complains if I > replace c.c7link with NULL in line 1. > > ---------------------------------------------------------------------------- > ------------ > 1 SELECT l.ltg, t.diu , t.tgno , t.cic , l.load_type , l.ltg_type, t.oml, > c.c7link > 2 FROM tbl_ltg l , tbl_tgrp t, tbl_c7links c > 3 WHERE l.ltg=t.ltg > 4 AND l.node=t.node > 5 AND l.node='BCO' > 6 > 7 UNION ALL > 8 > 9 SELECT l.ltg, t.diu, NULL, NULL, NULL, NULL, NULL, c.c7link > 10 FROM tbl_ltg l , tbl_tgrp t , tbl_c7links c > 11 WHERE l.ltg NOT IN (SELECT ltg FROM tbl_c7links) > 12 AND t.diu NOT IN (SELECT diu FROM tbl_c7links) > 13 AND l.node='BCO' > > ORDER BY l.ltg > ---------------------------------------------------------------------------- > ------------ > > The tables are : > > tbl_ltg > -------- > node ltg load_type ltg_type > ---------+-------+-----------+----------+ > BCO | 1- 1 | 46 | LTGB | > BCO | 1- 2 | 46 | LTGB | > BCO | 1- 3 | 46 | LTGB | > > tbl_tgrp > --------- > node ltg diu tgno cic oml > ---------+-------+-----------+----------+-------+-------+ > BCO | 1- 1 | 0 | MTNHAB | 7 | | > BCO | 1- 1 | 1 | JSAMAB | 9 | | > BCO | 1- 1 | 2 | | | | > BCO | 1- 1 | 3 | | | | > tbl_c7links > ------------- > tbl_tgrp > > node c7link lcod silt ltg diu ts status > ---------+----------+-----------+----------+-------+-------+-------+-------- > -+ > BCO | C7MTNMA | 0 | 12 | 1- 1 | 0 | 16 | > | > BCO | C7JSAMA | 1 | 100 | 1- 1 | 1 | 16 | > | > > ---------------------------------------------------------------------------- > ----------------------- > --------------------------------------------OUTER JOIN RESULTS > ------------------------------------ > ---------------------------------------------------------------------------- > ----------------------- > > I need to a SQL statement that will return the following tuples: > ltg diu tgno cic load_type ltg_type oml c7link > silt lcod > ------+-----+---------+-----+-----------+----------+---------+----------+--- > ---+-----+ > 1- 1 | 0 | MTNHAB | 7 | 46 | LTGB | | C7MTNMA | 12 > | 0 | > 1- 1 | 1 | JSAMAB | 9 | 46 | LTGB | | C7JSAMA | > 100 | 1 | > 1- 1 | 2 | | | | | | | > | | > 1- 1 | 3 | | | | | | | > | | > > Hoosain Madhi > TELECOMMUNICATIONS ENGINEERING > VODACOM PTY LTD > tel : +27 11 653 5030 > fax : +27 11 653 5941 > email : hoosain.madhi@vodacom.co.za
В списке pgsql-general по дате отправления: