Mors Violenta Mors Violenta - 1 year ago 63
SQL Question

MySQL output by columns

I have database for football (soccer) statisitics. I want to show first three teams through seasons in this fashion

I have table for stats through seasons that contains this columns (and some other, not interesting in this case) called clubseason:

  • idClubSeason

  • idClub

  • idSeason

  • clubPosition

Next tables that I have are for clubs and seasons, each containg ids (idClub and idSeason) and names (name of club and season). So three tables in relational database. Is there help to output SQL query in php in that manner?

My query is like this:

SELECT `jos_igraciDB_club`.`name` , `jos_igraciDB_season`.`idSeason`, `jos_igraciDB_season`.`name`,`jos_clubseason`.`clubPosition`, `jos_igraciDB_club`.`idClub`
FROM `jos_clubseason`
LEFT JOIN `jos_igraciDB_club` ON `jos_clubseason`.`idClub` = `jos_igraciDB_club`.`idClub`
LEFT JOIN `jos_igraciDB_season` ON `jos_clubseason`.`idSeason` = `jos_igraciDB_season`.`idSeason`
ORDER BY `jos_clubseason`.`clubPosition` ASC

Answer Source

I don't know exact database structure that you have, so you'll have to join additional tables for names etc, but this should be enough to get you an idea how to solve it.!9/d22e29/6

  clubseason cs
  INNER JOIN clubseason first  ON first.season  = cs.season AND first.position  = 1 
  INNER JOIN clubseason second ON second.season = cs.season AND second.position = 2 
  INNER JOIN clubseason third  ON third.season  = cs.season AND third.position  = 3 
GROUP BY cs.season