MySQL query with UNION ALL gives error 1271

I've various tables for storing comments of various parts of a website that have the same structure. I want to moderate the comments on the admin panel but I can't do a page for each, so I want to select it all and then LIMIT it. I asked how to do this here on SO and they solved my question but I get the error: 1271 - Illegal mix of collations for operation 'UNION'.

SELECT  *
FROM    (
        SELECT  *
        FROM    (
                SELECT  *
                FROM  noticias_comentarios
                ORDER BY
                        ts_creado DESC
                LIMIT 10
                ) q
        UNION ALL
        SELECT  *
        FROM    (
                SELECT  *
                FROM carruseles_comentarios
                ORDER BY
                        ts_creado DESC
                LIMIT 10
                ) q
        ) q
JOIN    usuarios u
ON      u.id = q.id_usuario
ORDER BY
        ts_creado DESC
LIMIT   0, 10

All the 3 tables have the character set utf8 and the collation utf8_spanish_ci.

How can I solve it?

Thank you in advance.

UPDATED with the answer of Larry:

SELECT  *
FROM    (
   SELECT id, id_noticia, id_usuario, comentario, ts_creado
   FROM  noticias_comentarios
   O开发者_如何学编程RDER BY ts_creado DESC
   LIMIT 0, 10
  UNION ALL
   SELECT id, id_carrusel, id_usuario, comentario, ts_creado
   FROM carruseles_comentarios
   ORDER BY  ts_creado DESC
   LIMIT 0, 10
  ) q
JOIN    usuarios u  ON u.id = q.id_usuario
ORDER BY ts_creado DESC
LIMIT   0, 10

Now produces the error: 1221 - Incorrect usage of UNION and ORDER BY

Ouch. Things that strike me at first glance:

• I don't believe you need all those nested result sets.

• You are reusing the alias "q" for several different queries.

• You should not use SELECT * in UNIONs. You should specify which columns you want, and in which order (and they must match in the different result sets you UNION together).

Try the following:

SELECT  *
FROM    (
   (SELECT col1, col2. . .
   FROM  noticias_comentarios
   ORDER BY ts_creado DESC
   LIMIT 10)
  UNION ALL
   (SELECT col1, col2. . .
   FROM carruseles_comentarios
   ORDER BY  ts_creado DESC
   LIMIT 10)
  ) q
JOIN    usuarios u  ON u.id = q.id_usuario
ORDER BY ts_creado DESC
LIMIT   0, 10

(This assumes you want the multi-level limits — top ten of the twenty records consisting of the top ten noticias and top ten carruseles).

This error is caused by the first ORDER BY in the queries you are UNIONing together:

ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

You should just remove that ORDER BY and LIMIT and apply it to the results of the UNION ALL. Like this:

SELECT  *
FROM    (
   SELECT id, id_noticia, NULL as id_carrusel, id_usuario, comentario COLLATE utf8_spanish_ci, ts_creado
   FROM  noticias_comentarios
  UNION ALL
   SELECT id, NULL as id_noticia, id_carrusel, id_usuario, comentario, ts_creado
   FROM carruseles_comentarios
   ORDER BY  ts_creado DESC
   LIMIT 0, 10
  ) q
JOIN    usuarios u  ON u.id = q.id_usuario
ORDER BY q.ts_creado DESC
LIMIT   0, 10

make sure table carruseles_comentarios and noticias_comentarios have same number of columns: If they have different number of columns try to get only common columns like following:

SELECT  *
    FROM    (
            SELECT  col1, col2
            FROM  noticias_comentarios
            ORDER BY
                    ts_creado DESC
            LIMIT 10
            ) q
    UNION ALL
    SELECT  *
    FROM    (
            SELECT  col1, col2
            FROM carruseles_comentarios
            ORDER BY
                    ts_creado DESC
            LIMIT 10
            ) q
    ) q