Make teams combination in SQL without repetition

Asked

Viewed 78 times

0

Good morning,

I have a database here with the tables below, and I wanted to generate a table with the combination of the registered teams

Time
------
Time A
Time B
Time C
Time D

The final table would look like this:

TABELA 1 | TABELA 2
TIME A   | TIME B
TIME B   | TIME C
TIME C   | TIME D
TIME A   | TIME C
TIME D   | TIME A

The command I tested was this:

SELECT a1.nome_time, a2.nome_time 
FROM times a1, times a2
WHERE a1.nome_time < a2.nome_time;

how could you modify this command so that the table does not repeat in the second column?

mysql sql

  • Review your final table and check if this is the desired result.

  • What you could do would be a for in an outside language, to be able to pick a number and assign to the ID of this team and this number was not repeated

2 answers

0

Use the distinct, the sql would look like this:

"SELECT Distinct a1.time_name, a2.time_name FROM times a1, times a2 WHERE a1.time_name < a2.time_name;"

0


try something like that

SELECT a1.nome_time, a2.nome_time 
FROM times a1, times a2
WHERE a1.nome_time < a2.nome_time and a1.nome_time <> a2.nome_time;

  • If the condition a1.time_name < a2.time_name is met then certainly a1.time_name <> a2.time_name will be true. Douglas could explain better what the desired result.

  • It was really my mistake to find that conclusion, I didn’t understand much

  • Not necessarily Douglas.

  • I improved the table a little here and analyzing the code I added in the select above ORDER BY RAND (); As I had warned that this is just a test I’m doing to generate tables of games, the second column is no longer repeating the records.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.