sql self-join table remove duplicate lines

Question

I have the following table:

╔════════╦════════════╗ ║ USERID ║ LANGUAGEID ║ ╠════════╬════════════╣ ║      1 ║          2 ║ ║      1 ║          7 ║ ║      1 ║          8 ║ ║      2 ║         10 ║ ║      2 ║          3 ║ ╚════════╩════════════╝ 

now I want to create all the possible pairs of languages for each user which means that I want the result set to be: for user 1: (2,7), (7,8), (2,8)

for user 2: (10,3)

to do so I've done the following query:

SELECT a.userId , a.LanguageId, b.LanguageId FROM knownlanguages a, knownlanguages b   WHERE a.userID=b.userID   AND a.LanguageId<>b.LanguageId 

the result that i'm getting is for user 1: (2,7), (7,8), (2,8) , (7,2), (8,7), (8,2)

for user 2: (10,3), (3,10)

there is no difference for me between (10,3) and (3,10)

how can I remove the duplicate lines?

tnx

Answer 1

With your identifiers:

SELECT a.userId , a.LanguageId, b.LanguageId   FROM knownlanguages a inner join knownlanguages b       on a.userID=b.userID and a.LanguageId < b.LanguageId 

Testing: Fot table:

create table t ( u int, l int);  insert into t values  (    1,               2), (    1,               7), (    1,               8), (    2,               10), (    2,               3); 

The query is:

select t1.u, t1.l as l1, t2.l as l2 from t t1 inner join t t2    on t1.u = t2.u and t1.l < t2.l 

( Results)