Mors Violenta Mors Violenta - 4 months ago 17
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 http://footballfacts.ru/turnircats/105166-chempionat-sssr

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

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. http://sqlfiddle.com/#!9/d22e29/6

SELECT
  cs.season,
  first.club,
  second.club,
  third.club
FROM
  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