title: SQL Query problem - difficult query with six tables
body: <p>I need a help of a sql expert. I have a problem with my sql query. I have six MySQL tables:</p>
  
  <pre><code>pcs_persons (table of players)
  
  +----+------------+------------+
  | id |  firstname |  lastname  |
  +----+------------+------------+
  |  1 |  John      |  McClane   |
  |  2 |  Jack      |  Marriott  |
  |  3 |  Billy     |  Bravo     |
  |  4 |  Archie    |  MacDonald |
  +----+------------+------------+
  
  pcs_matchs (table of match results)
  
  +----+-------------------+-------------------+---------+------------+------------+
  | id |  id_candidate_dom |  id_candidate_ext |  id_day |  id_season |  id_compet |
  +----+-------------------+-------------------+---------+------------+------------+
  |  1 |                 1 |                 2 |       1 |          1 |          1 |
  |  2 |                 3 |                 4 |       1 |          1 |          1 |
  |  3 |                 2 |                 3 |       2 |          1 |          1 |
  |  4 |                 4 |                 1 |       2 |          1 |          1 |
  |  5 |                 1 |                 7 |       1 |          2 |          3 |
  |  6 |                 6 |                 3 |       2 |          2 |          5 |
  +----+-------------------+-------------------+---------+------------+------------+
  
  
  pcs_lineup (table of those players who were selected to the match squad as starter - type 2, or as substitute - type 3)
  
  +----+-----------+----------+------------+-------+
  | id |  id_match |  id_club |  id_person |  type |
  +----+-----------+----------+------------+-------+
  |  1 |         1 |        1 |          1 |     2 |
  |  2 |         1 |        1 |          2 |     3 |
  |  3 |         1 |        2 |          3 |     2 |
  |  4 |         1 |        2 |          4 |     3 |
  +----+-----------+----------+------------+-------+
  
  pcs_goals (table of scored goals by players)
  
  | id |  id_match |  id_person |  id_club |  goal_min |
  +----+-----------+------------+----------+-----------+
  |  1 |         1 |          1 |        1 |        23 |
  |  2 |         1 |          1 |        1 |        48 |
  |  3 |         1 |          3 |        2 |        56 |
  |  4 |         1 |          4 |        2 |        89 |
  +----+-----------+------------+----------+-----------+
  
  
  pcs_cards (table of received cards by players)
  
  | id |  id_match |  id_person |  id_club |  card_min |  card_yellow |  card_red |
  +----+-----------+------------+----------+-----------+--------------+-----------+
  |  1 |         1 |          1 |        1 |        12 |            1 |           |
  |  2 |         1 |          1 |        1 |        43 |            1 |           |
  |  3 |         1 |          3 |        2 |        78 |               |         1 |
  |  4 |         1 |          4 |        2 |        91 |            1 |           |
  +----+-----------+------------+----------+-----------+--------------+-----------+
  
  pcs_subs (table of substitutions)
  
  | id |  id_match |  id_club |  id_person_in |  id_person_out |  subs_min |
  +----+-----------+----------+---------------+----------------+-----------+
  |  1 |         1 |        1 |             7 |              1 |        82 |
  |  2 |         1 |        1 |             8 |              2 |        85 |
  |  3 |         1 |        2 |             5 |              3 |        89 |
  |  4 |         1 |        2 |             6 |              4 |        91 |
  +----+-----------+----------+---------------+----------------+-----------+
  </code></pre>
  
  <p>My current query is here:</p>
  
  <pre><code>SELECT pcs_lineup.id_person, pcs_lineup.id_club, pcs_lineup.type,
  pcs_persons.lastname, pcs_persons.firstname, count( pcs_lineup.id_person) AS apps, count(pcs_subs.id_person_in) AS subs
  FROM pcs_lineup
  JOIN pcs_matchs ON pcs_matchs.id = pcs_lineup.id_match
  JOIN pcs_persons ON pcs_persons.id = pcs_lineup.id_person
  LEFT JOIN pcs_subs ON pcs_subs.id_person_in = pcs_lineup.id_person
  WHERE pcs_lineup.id_club =2
  AND pcs_matchs.id_compet =1
  AND pcs_matchs.id_season =1
  AND pcs_lineup.type = 2 OR pcs_subs.id_person_in IS NOT NULL AND pcs_subs.id_club =2
  GROUP BY id_person
  </code></pre>
  
  <p>My current result structure (list of players who played as member of starting lineup or played as substitutes, players who just sat on the bench not counted)</p>
  
  <pre><code>+-----------+----------+-------+-----------+------------+-------+-------+
  | id_person |  id_club |  type |  lastname |  firstname |  apps |  subs |
  +-----------+----------+-------+-----------+------------+-------+-------+
  </code></pre>
  
  <p>I would like to add extra columns (goals, yellow cards, red cards) to the result, but I don't know how.</p>
  
  <p>The structure of desired result:</p>
  
  <pre><code>+-----------+----------+-------+-----------+------------+-------+----------+-------+--------+---------------+------------+
  | id_person |  id_club |  type |  lastname |  firstname |  apps |  starter |  subs |  goals |  yellow cards |  red_cards |
  +-----------+----------+-------+-----------+------------+-------+----------+-------+--------+---------------+------------+
  </code></pre>
  
  <p>I hope that some expert could help for me, because I have no idea how could I join these tables for the desired result. Many thanks!</p>
  
  <p>Modified code (results are not good)</p>
  
  <pre><code>SELECT pcs_lineup.id_person
        ,pcs_lineup.id_club
        ,pcs_lineup.type
        ,pcs_persons.lastname
        ,pcs_persons.firstname
        ,count( pcs_lineup.id_person) AS apps
        ,CASE WHEN pcs_lineup.type = 2 THEN 'YES' END starter
        ,count(pcs_subs.id_person_in) AS subs
        ,count(pcs_goals.goal_min) AS goals
        ,count(card_yellow) as "Yellow Cards"
        ,count(card_red) as "Red Card"
  FROM pcs_lineup
  JOIN pcs_matchs ON pcs_matchs.id = pcs_lineup.id_match
  JOIN pcs_persons ON pcs_persons.id = pcs_lineup.id_person
  LEFT JOIN pcs_subs ON pcs_subs.id_person_in = pcs_lineup.id_person
  LEFT JOIN pcs_goals ON pcs_goals.id_match = pcs_matchs.id
                      AND pcs_persons.id = pcs_goals.id_person
  LEFT JOIN pcs_cards ON pcs_cards.id_match = pcs_matchs.id
                      AND pcs_cards.id_person = pcs_persons.id
                      AND pcs_goals.id_club = pcs_cards.id_club
  WHERE pcs_lineup.id_club =2
  AND pcs_matchs.id_compet =1
  AND pcs_matchs.id_season =1
  AND pcs_subs.id_person_in IS NOT NULL AND pcs_subs.id_club =2
  GROUP BY id_person
  </code></pre>