PostgreSQL SQL - How can I selet rows which have 2 columns values cross equal?

Author

How can I selet rows which have 2 columns values cross equal?

Fay Du

2006-03-10, 9:25 am

Hi All:
I have a table like this:

Table test
Id | a | b
-----+----+---
1 | 100| 101
2 | 101| 100
3 | 100| 3
4 | 20 | 30
5 | 11 | 13
6 | 3 | 33
7 | 30 | 20

I want to get row 1, 2,4 and 7 selected. Because their values of a and b
are cross equal. i.e., for each pair of rows,
a.Row1 = b.Row2 and b.Ro1 = a.Row2
a.Row4 = b.Row7 and b.Ro4 = a.Row7

How can I construct a sql statement to select them?
Thanks in advance.
Fay

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Milorad Poluga

2006-03-10, 9:25 am

On Friday 10 March 2006 14:24, Fay Du wrote:
> _ _Table test
> Id _ | _a | b
> -----+----+---
> 1 _ _| 100| 101
> 2 _ _| 101| 100
> 3 _ _| 100| 3
> 4 _ _| 20 | 30
> 5 _ _| 11 | 13
> 6 _ _| 3 _| 33
> 7 _ _| 30 | 20
>

select a.*
from test a, test b
where a.a = b.b and a.b = b.a
order by id

Regards, Milorad Poluga

--
---------------------------------------
Milorad Poluga
HK CORES Beograd, Makenzijeva 31
milorad.poluga@cores.co.yu
---------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

PFC

2006-03-10, 9:25 am

What are your conditions on a and b ? Can a be equal to b on a row ? If
so, do you want this row ?
If you want to avoid duplicates, I suggest first removing them, then
adding a constraint CHECK( a<b ) for instance. Then, from you r
application (or in an ON INSERT trigger), swap a and b if a>b.

I added some values to your table for completeness :

SELECT * FROM test;
a | b | id
-----+-----+----
100 | 101 | 1
101 | 100 | 2
100 | 3 | 3
20 | 30 | 4
11 | 13 | 5
3 | 33 | 6
30 | 20 | 7
666 | 666 | 8
666 | 666 | 9
500 | 666 | 10
666 | 500 | 11
123 | 123 | 12
456 | 789 | 13
456 | 789 | 14

Try :

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
2 | 1 | 101 | 100 | 100 | 101
4 | 7 | 20 | 30 | 30 | 20
7 | 4 | 30 | 20 | 20 | 30
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
9 | 8 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
11 | 10 | 666 | 500 | 500 | 666
12 | 12 | 123 | 123 | 123 | 123

You'll get 2 rows for each match. You can add a condition to remove the
dupes :

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<=y.id;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
12 | 12 | 123 | 123 | 123 | 123

If you don't want the rows with a=b, replace x.id<=y.id with x.id<y.id

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<y.id;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500

It is going to be slow, though. Basically a full self join. Let's hack
this :

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

I'll leave this one as an exercice to the reader ;)))

SELECT array_accum(id), CASE WHEN a<b THEN a ELSE b END AS low, CASE WHEN
a<b THEN b ELSE a END AS high FROM test GROUP BY low, high HAVING
sum(sign(a-b)) = 0 AND count(*)>1;
array_accum | low | high
-------------+-----+------
{10,11} | 500 | 666
{4,7} | 20 | 30
{1,2} | 100 | 101
{8,9} | 666 | 666

On Fri, 10 Mar 2006 14:24:44 +0100, Fay Du <fay.du@versaterm.com> wrote:

> Hi All:
> I have a table like this:
>
> Table test
> Id | a | b
> -----+----+---
> 1 | 100| 101
> 2 | 101| 100
> 3 | 100| 3
> 4 | 20 | 30
> 5 | 11 | 13
> 6 | 3 | 33
> 7 | 30 | 20
>
> I want to get row 1, 2,4 and 7 selected. Because their values of a and b
> are cross equal. i.e., for each pair of rows,
> a.Row1 = b.Row2 and b.Ro1 = a.Row2
> a.Row4 = b.Row7 and b.Ro4 = a.Row7
>
> How can I construct a sql statement to select them?
> Thanks in advance.
> Fay
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Bruno Wolff III

2006-03-10, 1:23 pm

On Fri, Mar 10, 2006 at 08:24:44 -0500,
Fay Du <fay.du@versaterm.com> wrote:
> Hi All:
> I have a table like this:
>
> Table test
> Id | a | b
> -----+----+---
> 1 | 100| 101
> 2 | 101| 100
> 3 | 100| 3
> 4 | 20 | 30
> 5 | 11 | 13
> 6 | 3 | 33
> 7 | 30 | 20
>
> I want to get row 1, 2,4 and 7 selected. Because their values of a and b
> are cross equal. i.e., for each pair of rows,
> a.Row1 = b.Row2 and b.Ro1 = a.Row2
> a.Row4 = b.Row7 and b.Ro4 = a.Row7
>
> How can I construct a sql statement to select them?
> Thanks in advance.

Self join the table.
Something like (untested):

SELECT t1.id, t1.a, t1.b
FROM test t1, test t2
WHERE
t1.a = t2.b
AND
t1.b = t2.a
ORDER BY t1.id
;

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Glaesemann

2006-03-11, 3:23 am

On Mar 10, 2006, at 22:24 , Fay Du wrote:

> I want to get row 1, 2,4 and 7 selected. Because their values of a
> and b
> are cross equal. i.e., for each pair of rows,
> a.Row1 = b.Row2 and b.Ro1 = a.Row2
> a.Row4 = b.Row7 and b.Ro4 = a.Row7

You need to use subqueries:

create table test
(
id integer primary key
, a integer not null
, b integer not null
);

copy test (id, a, b) from stdin;
1 100 101
2 101 100
3 100 3
4 20 30
5 11 13
6 3 33
7 30 20
\.

select t1.id as t1_id, t2.id as t2_id
from test t1
join test t2 on (t1.a = t2.b and t1.b = t2.a);
t1_id | t2_id
-------+-------
7 | 4
4 | 7
2 | 1
1 | 2
(4 rows)

And if you don't want to have each pair listed twice, just add WHERE
t1.a < t2.a, e.g.,

select t1.id as t1_id, t2.id as t2_id
from test t1
join test t2 on (t1.a = t2.b and t1.b = t2.a)
where t1.a < t2.a;
t1_id | t2_id
-------+-------
4 | 7
1 | 2
(2 rows)

Hope this helps!

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Michael Glaesemann

2006-03-11, 7:23 am

On Mar 11, 2006, at 16:46 , Michael Glaesemann wrote:

> select t1.id as t1_id, t2.id as t2_id
> from test t1
> join test t2 on (t1.a = t2.b and t1.b = t2.a)
> where t1.a < t2.a;
> t1_id | t2_id
> -------+-------
> 4 | 7
> 1 | 2
> (2 rows)

Just a follow-up (mostly to myself): I've been toying with using
natural joins recently, and here's the same query rewritten to use a
natural join:

select id as t1_id, t2_id
from test t1
natural join (
select id as t2_id
, a as b
, b as a
from test
) t2
where id < t2_id;
t1_id | t2_id
-------+-------
4 | 7
1 | 2
(2 rows)

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links