LEFT OUTER JOINS - Mailing list pgsql-general

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