CODEWITHSUNDEEP
mysqlsqlselectduplicates
hvertous
hvertous

Reputation: 1173

sql select with no duplicates in both columns

I want to select rows without duplicate values in columns. What I mean is that if there is a row with | 2 | 1 | and another one with | 1 | 2 | in the current selection, I want to show only one of them.

+------+------+
| id1  | id2  |
+------+------+
|    2 |    1 |
|    4 |    3 |
|    3 |    4 |
|    1 |    4 |
+------+------+

so in the example above it will select only first, last and either second OR third row.

and also to substitute these values with the string 'TITLE' from another table.

table values: 

 +----+----------+
 | id | title    |
 +----+----------+
 |  1 | title1   |
 |  2 | title2   |
 |  3 | title3   |
 |  4 | title4   |
 +----+----------+

so that the final select would have only titles in rows.

Upvotes: 0

Views: 69

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use least and greatest to do this. least gets the lower value of id1,id2 and greatest gets the greater of id1,id2.

select distinct least(id1,id2),greatest(id1,id2)
from t

Actually the above generates rows which aren't in the table. To avoid it, you need a left join with a derived table.

select t1.id1,t1.id2 
from t t1
left join (select least(id1,id2) as id1,greatest(id1,id2) as id2
           from t
           group by least(id1,id2),greatest(id1,id2)
           having count(*) > 1
          ) t2 on t2.id1=t1.id1 and t2.id2=t1.id2
where t2.id1 is null and t2.id2 is null

Edit: To get the title strings from a different table based on id's

select t1.id1,t1.id2,tt1.title,tt2.title 
from t t1
left join (select least(id1,id2) as id1,greatest(id1,id2) as id2
           from t
           group by least(id1,id2),greatest(id1,id2)
           having count(*) > 1
          ) t2 on t2.id1=t1.id1 and t2.id2=t1.id2
join titles tt1 on tt1.id=t1.id1 --use a left join if the titles table won't have all the id's
join titles tt2 on tt2.id=t1.id2 --use a left join if the titles table won't have all the id's
where t2.id1 is null and t2.id2 is null

Upvotes: 2

Related Questions