CODEWITHSUNDEEP
sql
Leslie
Leslie

Reputation: 3644

SQL Query Produce all Combinations

I have searched and looked but can't quite get the SQL query to work as I need. I have this data:

10019201        GEE78316634770
10019201        SIE78308706478
10019202        GEE78316634773
10019202        SIE78308706477
10019202        SIE78308706499
10019202        SIE78308706900
10019501        SIE78308706917
10019501        GEE78316634930

I want to create the following output based on the fact that the first column match, the following output:

GEE78316634770     SIE78308706478
SIE78308706478     GEE78316634770
GEE78316634773     SIE78308706477
GEE78316634773     SIE78308706499
GEE78316634773     SIE78308706900
SIE78308706477     GEE78316634773
SIE78308706477     SIE78308706499
SIE78308706477     SIE78308706900
SIE78308706499     GEE78316634773
SIE78308706499     SIE78308706477
SIE78308706499     SIE78308706900
SIE78308706900     GEE78316634773
SIE78308706900     SIE78308706477
SIE78308706900     SIE78308706499
SIE78308706917     GEE78316634930
GEE78316634930     SIE78308706917

So the first two records with 10019201 are matched up in the results as 

GEE78316634770     SIE78308706478
SIE78308706478     GEE78316634770

the next set of records with 10019202 - there are four matches so the results are:

GEE78316634773     SIE78308706477
GEE78316634773     SIE78308706499
GEE78316634773     SIE78308706900
SIE78308706477     GEE78316634773
SIE78308706477     SIE78308706499
SIE78308706477     SIE78308706900
SIE78308706499     GEE78316634773
SIE78308706499     SIE78308706477
SIE78308706499     SIE78308706900
SIE78308706900     GEE78316634773
SIE78308706900     SIE78308706477
SIE78308706900     SIE78308706499

each one matched with the other 3.

Can someone help me with the query to get the results I need?

Thanks! Leslie

Upvotes: 0

Views: 35

Answers (1)

D Stanley
D Stanley

Reputation: 152556

Use a self-join:

{using column names ID and Value just to show syntax}

SELECT 
    t1.Value Value1,
    t2.Value Value2
FROM table t1
INNER JOIN table t2 
    ON t1.ID = t2.ID
    AND t1.Value <> t2.Value

Upvotes: 3

Related Questions