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:
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
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
SELECT
jos_igraciDB_season.name,
cnf.name, cns.name, cnt.name,
cs.idSeason,
first.idClub,
second.idClub,
third.idClub
FROM
jos_clubseason cs
INNER JOIN jos_clubseason first ON first.idSeason = cs.idSeason AND first.clubPosition = 1
INNER JOIN jos_clubseason second ON second.idSeason = cs.idSeason AND second.clubPosition = 2
INNER JOIN jos_clubseason third ON third.idSeason = cs.idSeason AND third.clubPosition = 3
LEFT JOIN jos_igraciDB_club cnf ON first.idClub = cnf.idClub AND first.clubPosition = 1
LEFT JOIN jos_igraciDB_club cns ON second.idClub = cns.idClub AND second.clubPosition = 2
LEFT JOIN jos_igraciDB_club cnt ON third.idClub = cnt.idClub AND third.clubPosition = 3
LEFT JOIN jos_igraciDB_season ON cs.idSeason = jos_igraciDB_season.idSeason
GROUP BY cs.idSeason