CODEWITHSUNDEEP
mysqlsql
data_person
data_person

Reputation: 4470

combinations in sql without repetitions

Use case:

Table_name : Teams. it has 1 column "Country)

Country
India
lanka
aus

Now I need to create a new table with 2 columns "Team 1" and Team 2" which generates the matches.

Example:

Team1     Team2        
Ind       Lanka
Ind       Aus

Ignoring the create table and insert table queries, here is my select query to accomplish this:

SELECT distinct
s1.Country as sk, 
s2.Country as sk2
from Teams s1 cross join Teams s2
where sk <> sk2

I am able to get a partial output, but my problem is the above query contains op like below:

Team1     Team2
India      Aus
Aus        India

but in the scenario these are two same matches. Any suggestions on how to correct this mistake is appreciated.

Upvotes: 1

Views: 2612

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use <:

select s1.Country as sk, s2.Country as sk2
from Teams s1 join
     Teams s2
     on s1.sk < s2.sk;

Note that you cannot use column aliases in the where clause. Instead, qualify all column references.

If country is repeated in the table, then use select distinct:

select distinct s1.Country as sk, s2.Country as sk2
from Teams s1 join
     Teams s2
     on s1.sk < s2.sk;

But if the countries are unique, don't bother. select distinct is more expensive than select and should only be used when needed.

Upvotes: 4

Related Questions