CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008t-sql
pili
pili

Reputation: 825

Sql query to get Unique Field combinations

I am trying to get the below results from a Single Table which contains Codes.

From :
CodesMaster Table
-----------
Code
-----
1
2
3
4

To : 
Code1 Code2
----- -----
2   1
3   1
4   1
3   2
4   2
4   3

This Query returns all combinations.

select C1.Code, C2.Code from 
    CodesMaster C1, CodesMaster C2 
    where C1.Code <> C2.Code

But I need to filter the unique combinations. Eg : if 1 - 2 exists then I don't need 2 - 1

EDIT : IDS are not always Integers. In real it will be like : ADE1, BDE3

Upvotes: 0

Views: 77

Answers (2)

Roman Sergeev
Roman Sergeev

Reputation: 196

select C1.Code, C2.Code from 
CodesMaster C1, CodesMaster C2 
where C1.Code < C2.Code

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

select C1.Code, C2.Code 
from CodesMaster C1
inner join CodesMaster C2 ON C1.Code > C2.Code

Upvotes: 4

Related Questions