CODEWITHSUNDEEP
mysqlsqlunionunion-all
black-room-boy
black-room-boy

Reputation: 659

How to use MySQL UNION inside UNION ALL

I am trying to execute this query:

(
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (31, 791, 360, 382)
GROUP BY offerId
UNION
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (869, 376, 201, 1246)
GROUP BY offerId
ORDER BY rank DESC
)
UNION ALL
(
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (117, 168, 193, 204, 330, 377, 378, 379, 380, 381, 452, 931, 980, 1100, 1146, 1147, 1190, 1247)
GROUP BY offerId
ORDER BY rank DESC
)

But I get error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

So, I guess that either I am using wrong syntax or I am trying to do something that can not be done. I guess that SQL fiddle is not needed for this, most likely experienced people will see right away what is wrong.

Can someone help ? Thanks

Upvotes: 1

Views: 1384

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

You are using parentesis in a wrong way: the way you put in your code seems that you are trying to establish precedence order between selects unioned statmets, and that is not allowed by the syntax. But as was mention by some one on the coments, you can:

select * from
    (select ... from ...
    union
    select ... from ...) as T1
union all
    select ... from ...

Where all sides of union/union all are single queryes.

Some minor issues:
order by inside each select has no sense if no limit clause is present.
not in all cases group by (using mysql hability to miss grouping functions) results the same result that distinct

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Your error is coming from your parentheses. Here's one way to rewrite your current query:

SELECT offerId, title, details, link, reference, rank
FROM
(
   SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
   FROM club_offer co
   WHERE co.id IN (31, 791, 360, 382)
   UNION
   SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
   FROM club_offer co
   WHERE co.id IN (869, 376, 201, 1246)
) t
UNION ALL
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (117, 168, 193, 204, 330, 377, 378, 379, 380, 381, 
                452, 931, 980, 1100, 1146, 1147, 1190, 1247)
ORDER BY rank DESC

I've removed the group by and order by clauses from the inner queries as well -- feel free to add back as needed, but the order by in the subquery would be useless.

Upvotes: 2

Related Questions