Message-ID: <47F3967C.9070104@encs.concordia.ca>
Date: Wed, 02 Apr 2008 10:21:48 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Thunderbird 2.0.0.12 (X11/20080213)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: A sys func for a->b,   b->c =>     a->c ?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Good Morning,

Someone has better solution about the following query situation?

table test with two columns with primary key (id1, id2)
id1, id2
=========
1    2
1    3
12   3
13   5


Query conditions:
=====================
(1) a->b  => b->a
(2) a->b and a->c => a->c


Expected return:
id1   id2
===========
1     2
1     3
1    12

2    1
2    3
2    12

3    1
3    2
3    12

12   1
12   2
12   3

13   5


I did:

create view v_test AS
select id1 , id2 from test
union
select id2, id1  from test;


(
SELECT    a.id1 , b.id2
FROM      v_test AS a
left join v_test AS b
    ON (a.id2 = b.id1)
WHERE a.id1 <> b.id2
)
UNION
(
SELECT id1, id2
FROM   v_test
)
order by id1 ;


The query is a bit complex, do we have a better system func or query for 
this?

Thanks a lot!