CODEWITHSUNDEEP
mysqlsqldistinct
Alnedru
Alnedru

Reputation: 2655

MySql select multiple column unique row

I have a small difficulty with writing a query:

I have following table:

ID   |   FromId   |  ToId
--------------------------
1         10          15
2         10          15
3         15          10
4         15          10
5         15          20
6         14          15
7         20          15
8         20          10
9         10          20
10        10           1

No i would like to get from here unique wich has value of 10

And by unique i mean for example, the result which i would like to accomplish, if the variable is 10

FromId   |   ToId
------------------
10            15
20            10
10             1

I'm ofcourse not sure if it is possible to accomplish something like that tho ...

For me in this case 

10   20 == 20   10

But for sql not :/

I think ppl dont understand me completly...

I need uniqueness in the combination with 10

Upvotes: 0

Views: 89

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can do this as:

select least(fromId, toId) as fromId, greatest(fromId, toId) as toId
from MyTable t
where fromId = 10 or toId = 10
group by least(fromId, toId), greatest(fromId, toId);

EDIT:

(In response to your question about performance.)

You should have indexes on fromId and toId.

Your original data has duplicate values, so the duplicates have to be removed. In MySQL, this requires sorting the data, either through an explicit order by or by using group by (and the associated filesort). The above query should perform as well as any query that uses distinct.

If you have two indexes, on t(fromId, toId) and t(toId, fromId) then the following might perform better:

select distinct fromId, toId
from MyTable t
where fromId = 10
union all
select distinct fromId, toId
from MyTable t
where toId = 10 and
      not exists (select 1 from MyTable t2 where t2.fromId = t.fromId and t2.toId = t.fromId);

MySQL can perform the distinct by scanning the index. I'm not sure if it does in practice.

Upvotes: 3

Senthilmurugan
Senthilmurugan

Reputation: 383

Try Distinct with OR.

select distinct FromId,ToId from Test where FromId = 10 OR ToId=10;

http://sqlfiddle.com/#!2/18384/3

Upvotes: 0

youssefhassan
youssefhassan

Reputation: 1065

SELECT DISTINCT FromId,ToId FROM myTable  WHERE FromId = 10 OR ToId = 10

Upvotes: 0

Vikas Sangle
Vikas Sangle

Reputation: 632

I think this should work,

SELECT * FROM myTable WHERE FromId = 10 OR ToId = 10;

Upvotes: 2

Related Questions