UNION query error - number of columns do not match

[selahlynch]

The following query works without a problem:

SELECT *
FROM
( SELECT Name, 99999 AS EmpNumb, 2 AS Origin FROM [SS Training] )
UNION ALL
( SELECT Name, EmpNum AS EmpNumb, 3 AS Origin FROM [JS Driving] )

However this query gives an error:
"The number of columns in the two selected tables or queries of a union query do not match."

SELECT Name, EmpNumb
FROM
( SELECT Name, 99999 AS EmpNumb, 2 AS Origin FROM [SS Training] )
UNION ALL
( SELECT Name, EmpNum AS EmpNumb, 3 AS Origin FROM [JS Driving] )

Any idea why? I'm confused.

 

[stopher]

The section before the UNION line is using a sub-query with the end result of selecting two fields: Name and EmpNumb

So your query boils down to:

SELECT Name, EmpNumb
UNION ALL
SELECT Name, EmpNum AS EmpNumb, 3 AS Origin FROM [JS Driving]

As you can see, the bit before the UNION has only 2 columns but the bit after has 3 columns. Hence the query comes unstuck.

You don't need a sub-query as far as I can see so the following should be fine:

SELECT Name, 99999 AS EmpNumb, 2 AS Origin FROM [SS Training] 
UNION ALL
SELECT Name, EmpNum AS EmpNumb, 3 AS Origin FROM [JS Driving]

Or if you only want the first two columns:

SELECT Name, 99999 AS EmpNumb FROM [SS Training]
UNION ALL
SELECT Name, EmpNum AS EmpNumb FROM [JS Driving]

hth
Chris

 

[selahlynch]

Thanks, that did help. Another question for you...

Do you know why this works:

SELECT * FROM
(
SELECT 1 AS num, 'abc' AS str FROM Events
UNION 
SELECT 3 AS num, 'abc' AS str FROM Events
UNION 
SELECT 9 AS num, 'xyz' AS str FROM Events
)
;

But this gives an error "Syntax error in JOIN operation":

SELECT * FROM
(
(SELECT 1 AS num, 'abc' AS str FROM Events)
UNION 
(SELECT 3 AS num, 'abc' AS str FROM Events)
UNION 
(SELECT 9 AS num, 'xyz' AS str FROM Events)
)
;

I tend to be a little parenthesis happy... but it seems this can cause problems with SQL.

 

[stopher]

Thanks, that did help. Another question for you...

Do you know why this works:

SELECT * FROM
(
SELECT 1 AS num, 'abc' AS str FROM Events
UNION 
SELECT 3 AS num, 'abc' AS str FROM Events
UNION 
SELECT 9 AS num, 'xyz' AS str FROM Events
)
;

But this gives an error "Syntax error in JOIN operation":

SELECT * FROM
(
(SELECT 1 AS num, 'abc' AS str FROM Events)
UNION 
(SELECT 3 AS num, 'abc' AS str FROM Events)
UNION 
(SELECT 9 AS num, 'xyz' AS str FROM Events)
)
;

I tend to be a little parenthesis happy... but it seems this can cause problems with SQL.

I'm not 100% sure why the second expression does not work but I suspect that UNION requires a SELECT statement (text string) as its arguement but by putting brackets round it you are forcing the SELECT expression to be processes first and therefore submitting a data set as the argument.

In any case, you are again using a sub-query. This is pointless and will onlhy make your SQL look far more confusing. The following will suffice:

SELECT 1 AS num, 'abc' AS str FROM Events
UNION 
SELECT 3 AS num, 'abc' AS str FROM Events
UNION 
SELECT 9 AS num, 'xyz' AS str FROM Events

Chris

 

[selahlynch]

FYI: I kept that statement as a subquery because this is the actual statement I am using in my database:

SELECT DISTINCT UnionTable.Name, UnionTable.EmpNumb
FROM (
SELECT Name, 99999 AS EmpNumb, 1 AS Origin FROM [SS Driving]
UNION
SELECT Name, 99999 AS EmpNumb, 2 AS Origin FROM [SS Training]
UNION
SELECT Name, EmpNum AS EmpNumb, 3 AS Origin FROM [JS Driving] 
UNION
SELECT Name, EmpNum AS EmpNumb, 4 AS Origin FROM [JS Training] 
)  AS UnionTable;

I'm working at merging four different spreadsheets of employee information. It is tricky because there are Arab employees and there is a lot of inconsistent spelling of employee names from one sheet to the next.

Anyway, thanks again for your help.

 

[stopher]

FYI: I kept that statement as a subquery because this is the actual statement I am using in my database:

Fair point. Good luck with your merging.
Chris