Selecting same pair only ones ! 1

[mkyllone]

HI,

I have a db table where first two columns are
HOME and NEIGHBOUR and simplistic data in these columns
is

HOME NEIGHBOUR
a b
a c
a d
b a
b c
b d
c a
c b
c d
d a
d b
d c

Now, as you can see from the data, that same pair can be
presented in two ways e.g. a,b <-> b,a.

How to build a select query that fetches same pair only ones. Created help_table would have data like this
HOME NEIGHBOUR
a b
a c
a d
b c
b d
c d

BIG Thank you, on addvance

 

[PHV]

Try this:

SELECT DISTINCT HOME,NEIGHBOUR
  FROM table1 A
 WHERE NOT EXISTS(
SELECT * FROM table1 B
 WHERE B.NEIGHBOUR=A.HOME AND B.HOME=A.NEIGHBOUR
)

Hope This Help
PH.

 

[mkyllone]

HI PH, thanks for your help, but this select statement that you suggest to try fetches only those pairs that are not
pairs both ways a,b b,a. this i did know on addvance.

I have tryed to use B.NEIGHBOUR=A.HOME AND B.HOME=A.NEIGHBOUR in select statement for my problem
but OFFCOURCE it doesn't know that same variable b was
read as neighbour for a and therefore when proggramm comes to the line of b,a pair the same comparing that was done for a,b is done here also and do'nt exclude it from fetch.

Are you (or some one else) familiar with PL/SQL language.
I know that this could be resolved with it but i have so little experience with PL/SQL that it would take days to do it.

The big idea is in steps:
1. first the proggramm reads first line from the source table to (i call it as a table A)
2. first line is added to A_help_tbl with X to column called opposite
3. second line is fetched into variableA and variableB
4. second cursor2 is opened and it reads lines from
A_help_tbl using variableA and variableB
were opposite = X and (NEIGHBOUR=variableA AND HOME=variableB) OR (NEIGHBOUR=variableB AND HOME=variableA)
IF line is found proggramm reads the next line from table A
IF line not found proggramm closes cursor2 and variables A and B are written to A_help_table with X -mark.
5. adn so on and so on....

Do some one know how to pass variables for cursor and
is it possible to do that writing closing reading thing to a table that is used as a cursor too.

Thanks.

 

[r937]

if only one pair exists, take it -- that's the first query in the UNION below

but if both pairs exist in the table, we take only one of them, the one that has the lower value for HOME -- that's the second query in the UNION

[tt]select t1.home, t1.neighbour
from yourtable t1
left outer
join yourtable t2
on t1.home = t2.neighbour
and t1.neighbour = t2.home
where t2.neighbour is null
union all
select case when t1.home < t2.home
then t1.home
else t2.home end
, case when t1.home < t2.home
then t1.neighbour
else t2.neighbour end
from yourtable t1
inner
join yourtable t2
on t1.home = t2.neighbour
and t1.neighbour = t2.home[/tt]

rudy

http://r937.com/

SQL Consulting

 

[dnoeth]

select home, neighbour
from yourtable

union

select neighbour, home
from yourtable

Dieter

 

[r937]

dieter, i don't think that will do the job

if {a,b} and {b,a} both exist in the table, they will both be returned by the first subquery in your union, while the second subquery in your union will return {b,a} and {a,b}

then out of those 4 rows, the UNION will eliminate dupes, and the result will be two rows -- {a,b} and {b,a}

which is what mkyllone is trying to avoid

i did not actually test my solution, but i have confidence in it



rudy

http://r937.com/

SQL Consulting

 

[swampBoogie]

select distinct case when home < neighbour then
 home else neighbour end, 
case when home < neighbour then 
neighbour else home end 
from t;

 

[r937]

well, yes, swampboogie, that works, except it reverses all values that aren't sequenced ( a < b ) whether or not they are singletons (rows without a &quot;matching&quot; pair in the opposite sequence)

i suppose that's just as arbitrary as my solution which chooses the sequenced pair when there are matches

still, my solution does not alter any sequences, all rows returned are actually in the table, it just throws the unsequenced dupes away

your solution returns rows that weren't in the original table

a minor point, to be sure

rudy

http://r937.com/

SQL Consulting

 

[dnoeth]

aaaargh, rudy's right, of course...

Maybe i should think before i post ;-)

Dieter

 

[dnoeth]

This keeps the sequence of columns, i hope i got it right this time...

select t1.home, t1.neighbour
from
yourtable t1 left outer join yourtable t2
on t1.home = t2.neighbour
and t1.neighbour = t2.home
and t1.home > t2.home
where t2.home is null;

Dieter

 

[r937]

beautiful!! yes it works!

when i saw your solution, i could resist no longer, i had to create a little test table and try it

turns out mine actually didn't work correctly (both pairs were returned in the second half of the union)

but yours is much more elegant, dieter

good job



rudy

http://r937.com/

SQL Consulting

 

[PHV]

So, this should does the trick too ?

SELECT DISTINCT HOME,NEIGHBOUR
  FROM table1 A
 WHERE NOT EXISTS(
SELECT * FROM table1 B
 WHERE B.NEIGHBOUR=A.HOME AND B.HOME=A.NEIGHBOUR
   AND B.HOME<A.HOME
)

 

[r937]

yes -- even without the DISTINCT

;-)

rudy

http://r937.com/

SQL Consulting