Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
legend986
Forum Contributor
Posts: 258 Joined: Sun Jul 15, 2007 2:45 pm
Post
by legend986 » Wed Oct 29, 2008 6:40 pm
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:
Can someone please help me out?
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 29, 2008 6:52 pm
[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
legend986
Forum Contributor
Posts: 258 Joined: Sun Jul 15, 2007 2:45 pm
Post
by legend986 » Wed Oct 29, 2008 7:02 pm
Perfect! Thank you so much... Really nice logic...
legend986
Forum Contributor
Posts: 258 Joined: Sun Jul 15, 2007 2:45 pm
Post
by legend986 » Wed Oct 29, 2008 7:12 pm
Just a final extension... Assuming that I have done that on two tables, I want to select the intersection rows. So something like:
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.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 29, 2008 7:18 pm
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
legend986
Forum Contributor
Posts: 258 Joined: Sun Jul 15, 2007 2:45 pm
Post
by legend986 » Wed Oct 29, 2008 7:20 pm
Yes...
I am trying it in parallel here... Will post it the moment I get it.... Thank You for encouraging...
legend986
Forum Contributor
Posts: 258 Joined: Sun Jul 15, 2007 2:45 pm
Post
by legend986 » Wed Oct 29, 2008 7:37 pm
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.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 29, 2008 7:52 pm
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