CODEWITHSUNDEEP
sqlselect
Paiku Han
Paiku Han

Reputation: 583

How to SELECT all pairs of ID except when IDs are the same or the pair already exist (even in reverse order)

I've got a relation/table and I am trying to put together (select) each ID with each others without taking any result where the ID is twice in the pair nor the pairs that are already present in reverse order (i.e pair [1,2] and pair [2,1] are the same) I only manage to remove the pairs where the ID is repeated twice (i.e [1,1], [2,2], [3,3] and so on) by doing so:

SELECT a.id first, b.id second
FROM myrelation a, myrelation b
WHERE a.id != b.id;

I went from this:

    |ID|attr1|attr2|...
    |1 |value|value|...
    |2 |value|value|...
    |3 |value|value|...

to this:

    |first|second|
    |  1  |   2  |
    |  1  |   3  |
    |  2  |   1  |
    |  2  |   3  |
    |  3  |   1  |
    |  3  |   2  |

when I actually want this:

    |first|second|
    |  1  |   2  |
    |  1  |   3  |
    |  2  |   3  |

can anyone please help!!? Thank you

Upvotes: 0

Views: 1138

Answers (1)

sinsedrix
sinsedrix

Reputation: 4775

Very simple, take the diagonal of combination table (like @RamblinMan said):

SELECT a.id first, b.id second
FROM myrelation a, myrelation b
WHERE a.id < b.id;

Upvotes: 1

Related Questions