Re: LEFT OUTER JOINS - Mailing list 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