Mysql Subquery Unknown Column

With the following query I am getting the error unknown column a.id in where clause. I'm basically trying to add a limit of 5 to what would have been two left joins. Is it possible to rewrite this query so it can work?

SELECT 
    CONCAT_WS(' ',(
        SELECT GROUP_CONCAT(body,' ') FROM (
            SELECT c.body FROM c WHERE c.id IN (
                SELECT id_c FROM b WHERE b.id_a=a.id
            )
            LIMIT 5
        ) c
    )) AS contents
FROM
a

Full SQLfiddle at http://sqlfiddle.com/#!9/c1822/3

Find_in_set works http://sqlfiddle.com/#!9/2d43bb/1 but it is extremely slow with large datasets

Another way:

SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a 
  LEFT JOIN b
  ON b.id_a = a.id 
  LEFT JOIN c
  ON  b.id_c = c.id
  AND c.id <= COALESCE(
    (  SELECT ci.id
       FROM c AS ci
         JOIN b AS bi
         ON  bi.id_c = ci.id
       WHERE b.id_a = bi.id_a
       ORDER BY ci.id
       LIMIT 1 OFFSET 4 
    ), 10000000000)
GROUP BY a.id ;

a variation:

-- variation 2
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a 
  LEFT JOIN b
    JOIN c
    ON  b.id_c = c.id
    AND c.id <= COALESCE(
      (  SELECT ci.id
         FROM c AS ci
           JOIN b AS bi
           ON  bi.id_c = ci.id
         WHERE b.id_a = bi.id_a
         ORDER BY ci.id
         LIMIT 1 OFFSET 4 
      ), 10000000000)
  ON b.id_a = a.id 
GROUP BY a.id ;

and two more, all using the same basic pattern:

-- variation 3
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a 
  LEFT JOIN b
    ON  b.id_a = a.id 
    AND b.id_c <= COALESCE(
      (  SELECT bi.id_c 
         FROM b AS bi
         WHERE b.id_a = bi.id_a
         ORDER BY bi.id_c
         LIMIT 1 OFFSET 4 
      ), 10000000000)
  LEFT JOIN c
  ON b.id_c = c.id
GROUP BY a.id ;

-- variation 4
SELECT a.id,
       ( SELECT GROUP_CONCAT(c.body,' ')
         FROM b
         LEFT JOIN c
         ON b.id_c = c.id
         WHERE b.id_a = a.id 
           AND b.id_c <= COALESCE(
             ( SELECT bi.id_c 
               FROM b AS bi
               WHERE b.id_a = bi.id_a
               ORDER BY bi.id_c
               LIMIT 1 OFFSET 4 
             ), 10000000000)
       ) AS contents
FROM a ;

Tested in sqlfiddle.

StackExchange Database Administrators Q#205912, answer score: 4