0

I have table "teams" with fields: id, team_name

  id, team_name
  • 1, Real Madrid
  • 2, FC Barcelona
  • 3, juventus
  • 4, Milan

i want to select in one query all available VS combinations ...

like that:

Real Madrid - FC Barcelona

Real Madrid - juventus

Real Madrid - Milan

FC Barcelona - juventus

FC Barcelona - Milan

juventus - Milan

the purpose is to find solution for dynamic with different amount of rows if will be more teams in the tables...

this is possible? how... ?

Thanks :)

Community
  • 1
  • 1
shushu304
  • 1,506
  • 1
  • 7
  • 15
  • Possible duplicate of [Is there a way to simulate GROUP BY WITH CUBE in MySql?](https://stackoverflow.com/questions/7085236/is-there-a-way-to-simulate-group-by-with-cube-in-mysql) – RoMEoMusTDiE Dec 07 '17 at 21:05

1 Answers1

5

Use a self join.

select t1.team_name,t2.team_name
from tbl t1
join tbl t2 on t1.team_name>t2.team_name --or if the id's are unique use 
                                          /* on t1.id < t2.id */
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • to get the resultset specified in the question... `on t1.id < t2.id` (just a difference in which team is listed in column1 vs column2)... if the order of the rows is important, add an ORDER BY clause e.g. `ORDER BY t1.id, t2.id` +10 – spencer7593 Dec 07 '17 at 21:02
  • @spencer7593 .. you are right..i edited my answer to include it. – Vamsi Prabhala Dec 07 '17 at 21:04