Selecting distinct pairs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Selecting distinct pairs

Post by legend986 »

I have a table like:

Code: Select all

 
Column A | Column B
1 2
1 3
1 5
2 1
5 1
 


I am trying to select just the unique pairs from the above table to give me:

Code: Select all

 
1 2
1 3
1 5
 
Can someone please help me out?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting distinct pairs

Post by VladSun »

[sql]SELECT       a, b FROM     tGROUP BY     (a*b), (a+b)[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Re: Selecting distinct pairs

Post by legend986 »

Perfect! Thank you so much... Really nice logic...
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Re: Selecting distinct pairs

Post by legend986 »

Just a final extension... Assuming that I have done that on two tables, I want to select the intersection rows. So something like:

Code: Select all

 
Table 1
1 2
1 5
2 3 
4 5
 

Code: Select all

 
Table 2
2 1
8 9
 
When I query for the column1=1, I want to get only those elements from Table 1 that are not in Table 2 in either format so in this case, I should get only 1,5 because either 1,5 or 5,1 do not exist in Table 2.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting distinct pairs

Post by VladSun »

It's your turn to show us some code ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Re: Selecting distinct pairs

Post by legend986 »

Yes... :) I am trying it in parallel here... Will post it the moment I get it.... Thank You for encouraging...
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Re: Selecting distinct pairs

Post by legend986 »

Ok... I think I got it... Here's my solution:

Code: Select all

 
SELECT * from table1 
LEFT JOIN 
(SELECT column1,column2 FROM table2 UNION SELECT column2,column1 from table2) as TEMP 
USING (column1,column2) 
WHERE (TEMP.column1 IS NULL) AND (table1.column1=1 OR table1.column2=1);
 
I am getting the correct result using my test tables but I wanted to confirm it from you before I use it on my original dataset. If the solution is correct, thank you for not providing the solution and making me think. If it isn't, I hope you can direct me a little more. :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting distinct pairs

Post by VladSun »

Looks good to me :)
legend986 wrote:... thank you for not providing the solution and making me think ...
You are welcome ... anytime! :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply