What's wrong with my UNION SELECT

SELECT * 
FROM 
(SELECT Campus_ID AS A_Campus, * FROM A_Campuses
UNION
SELECT Ca开发者_运维百科mpus_ID AS H_Campus, * FROM H_Campuses
UNION
SELECT Campus_ID AS B_Campus, * FROM B_Campuses)
ORDER BY Campus_Name ASC

It gives the sql error

#1064 - 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 '* FROM A_Campuses UNION SELECT Campus_ID AS H_Campus, * FROM H_Campuses' at line 3

Step 1

You have two syntax errors

1. not aliasing the derived table.
2. * cannot be used AFTER a column name, unless you alias the source table

SELECT * 
FROM 
(SELECT Campus_ID AS A_Campus, A.* FROM A_Campuses A
UNION
SELECT Campus_ID AS H_Campus, H.* FROM H_Campuses H
UNION
SELECT Campus_ID AS B_Campus, B.* FROM B_Campuses B) AS X
ORDER BY Campus_Name ASC

Step 2

But as Phil points out, since you are sub-querying only to do an order by, there is no need to subquery at all. ORDER BY applies to the entire UNION-ed result.

SELECT Campus_ID AS A_Campus, A.* FROM A_Campuses A
UNION
SELECT Campus_ID AS H_Campus, H.* FROM H_Campuses H
UNION
SELECT Campus_ID AS B_Campus, B.* FROM B_Campuses B
ORDER BY Campus_Name ASC

Step 3

The next thing to point out is that A_, H_ and B_ must ALL have compatible structures for the UNION to align properly. It is also worth mentioning that aliasing Campus_ID as different column names has no value. The column names of the resultant result of a UNION is the FIRST name encountered across the UNION parts - in this case all the column names will come from A_Campuses, as well as the additional column A_Campus. In actual fact, you will have two columns A_Campus and Campus_ID which will always hold EXACTLY the same values. What you probably wanted was to indicate the SOURCE of the data: (notice that I have not even bothered to alias the columns for the 2nd and 3rd parts of the UNION)

SELECT 'A' AS Source, A.* FROM A_Campuses A
UNION ALL
SELECT 'H', H.* FROM H_Campuses H
UNION ALL
SELECT 'B', B.* FROM B_Campuses B
ORDER BY Campus_Name ASC

Note

For performance reasons, use UNION ALL instead of UNION, which performs a DISTINCT against the final result. If you had duplicate Campus_ID across different tables, as well as exactly the same record data, UNION results in one of them being removed, whereas UNION ALL keeps both (or all 3) copies. Given the addition of the Source column, this is not a possibility, so using UNION ALL will result in a faster query.

The columns in each branch of the UNION normally need the same name, or will end up with a single name. Also, a sub-select needs an alias (the 'AS C' below), at least in standard SQL; even if you don't mention the alias anywhere else in the query, as below.

I think what you're after is likely:

SELECT * 
  FROM (SELECT "A" AS Campus_ID, * FROM A_Campuses
        UNION
        SELECT "H" AS Campus_ID, * FROM H_Campuses
        UNION
        SELECT "B" AS Campus_ID, * FROM B_Campuses) AS C
 ORDER BY Campus_Name ASC

That isn't how you write a union query.

Any ORDER BY clause applies to the union so you don't need to sub-query it. Also, you should aim to have the same column names and aliases across all parts of the union. Your A_Campus, H_Campus and B_Campus aliases will be lost (not sure which one will win out). For example

SELECT Campus_ID, Campus_Name FROM A_Campuses
UNION
SELECT Campus_ID, Campus_Name FROM H_Campuses
UNION
SELECT Campus_ID, Campus_Name FROM B_Campuses
ORDER BY Campus_Name ASC

I'd also refrain from using SELECT * in a union as you need to be specific about what you're selecting.

perhaps more parenthesis are needed

SELECT * 
FROM 
(
  (SELECT Campus_ID AS A_Campus, * FROM A_Campuses)
  UNION
  (SELECT Campus_ID AS H_Campus, * FROM H_Campuses)
  UNION
  (SELECT Campus_ID AS B_Campus, * FROM B_Campuses)
)
ORDER BY Campus_Name ASC

Also can you UNION with different column names like that? You might need a fake A_Campus and B_Campus in the H_Campus subquery to get identical columns.