Thread: LEFT OUTER JOINS
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
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
Hoosain Madhi <MADHIH@vodacom.co.za> writes:
> 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.
As a general rule, you should provide the exact error message you are
getting when you post a question like this. "The parser complains"
is pretty unhelpful.
I am guessing that the issue is lack of datatype information in the
first select --- UNION currently takes the first select as determining
the types of all its result columns (which is a bug IMHO, but that's
how it acts at the moment). So try casting the NULL to the proper
datatype, eg if the column is text you want "NULL::text" or
"CAST(NULL AS text)".
regards, tom lane