sportsmanagementModelRosteralltime::getTeamPlayers 432
SELECT pr.firstname,pr.nickname,pr.lastname,pr.country,pr.birthday,pr.deathday,pr.id AS pid,pr.id AS person_id,pr.picture AS ppic,pr.suspension AS suspension,pr.away AS away,pr.injury AS injury,pr.picture AS ppic,CONCAT_WS(':',pr.id,pr.alias) AS person_slug,tp.id AS playerid,tp.id AS season_team_person_id,tp.jerseynumber AS position_number,tp.notes AS description,tp.market_value AS market_value,tp.market_text AS market_text,tp.picture,st.id AS season_team_id,pt.project_id AS project_id,pos.name AS position,ppos.position_id,ppos.id as pposid,CONCAT_WS(':',pro.id,pro.alias) AS project_slug,CONCAT_WS(':',t.id,t.alias) AS team_slug
FROM v8wvh_sportsmanagement_season_team_person_id AS tp
INNER JOIN v8wvh_sportsmanagement_season_team_id AS st ON st.team_id = tp.team_id and st.season_id = tp.season_id
INNER JOIN v8wvh_sportsmanagement_project_team AS pt ON pt.team_id = st.id
INNER JOIN v8wvh_sportsmanagement_person AS pr ON tp.person_id = pr.id
INNER JOIN v8wvh_sportsmanagement_project AS pro ON pro.id = pt.project_id and pro.season_id = st.season_id
INNER JOIN v8wvh_sportsmanagement_team AS t ON t.id = st.team_id
LEFT JOIN v8wvh_sportsmanagement_person_project_position AS perpos ON perpos.project_id = pro.id AND perpos.person_id = pr.id
LEFT JOIN v8wvh_sportsmanagement_project_position AS ppos ON ppos.id = perpos.project_position_id
LEFT JOIN v8wvh_sportsmanagement_position AS pos ON pos.id = ppos.position_id
WHERE tp.person_id IN ( 1781,447,1030,815,891,2426,450,514,1780,2344,1677,453,2546,362,1859,515,2630,2069,516,581 ) AND st.team_id = 159 AND pr.published = 1 AND tp.published = 1 AND tp.persontype = 1
GROUP BY pr.id
ORDER BY pos.ordering, ppos.position_id, tp.ordering, tp.jerseynumber, pr.lastname, pr.firstname
