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:
Show quoted text
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_tgrpnode 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