Home select all available combination by specific field in one query
Reply: 1

select all available combination by specific field in one query

shushu304
1#
shushu304 Published in 2017-12-07 20:55:27Z

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 :)

Vamsi Prabhala
2#
Vamsi Prabhala Reply to 2017-12-07 21:03:48Z

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 */
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.320347 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO