Problem with inner join

[joseluiselaprendiz]

Hello,
I have the follow table named A:

id  medicine
1   1
1   2
1   3

i wish to get the pairs of drugs that belong to the same id , for example:

medicine1 medicine2
1         2
1         3
2         3

i try to do this with this query:

select a1.medicine as medicine1, a2.medicine  as medicine2 
from  A  a1 inner join A  a2  on a1.id = a2.id and a1.medicine != a2.medicine

output:

medicine1 medicine2
1       2
2       1
1       3
3       1
2       3
3       2

i don't know how to do for to obtain only the pairs differtent, because (1,2) and (2,1) are the same, some can help me,please
thanks in advance

[coolsti]

select a1.medicine as medicine1, a2.medicine as medicine2 
from A a1 inner join A a2 on a1.id = a2.id and a1.medicine != a2.medicine and a1.medicine < a2.medicine

Won't the above work?

[Atli]

Please use [code] tags when posting your code examples. (See How to ask a question)

[code=sql] ...SQL query goes here... [/code]

Thank you.
Moderator

[coolsti]

Actually, thinking about it, my suggestion might not work.

If not, you could try this:

select distinct 
if(a1.medicine<a2.medicine,a1.medicine,a2.medicine) as medicine1, 
if(a1.medicine<a2.medicine,a2.medicine,a1.medicine) as medicine2 
from A a1 inner join A a2 on a1.id = a2.id 
and a1.medicine != a2.medicine and a1.medicine < a2.medicine 
 

Here I do the select as I suggested before, but I make sure that for each row selected, the two medicines that are taken as the two attributes in the resulting table are ordered so that medicine1 is always smaller than medicine2.

This would then transform the two pairs, e.g. "1 2" and "2 1" into two equal rows looking like "1 2".

Then I request that only "distinct" rows are returned. The distinct should then eliminate the duplicates that come about because of the ordering of the medicines.

.........................

Thinking about it some more (too lazy to test it), my original suggestion should work anyway. On the way to work today I thought of some reason why it would not, but now I cannot remember the reason :)