CODEWITHSUNDEEP
mysqlsqlunion-all
FosAvance
FosAvance

Reputation: 2469

UNION query doesn't work

This is my UNION ALL query but it won't work, I'm not sure why, it seems correct

        SELECT 
                ID AS IDzivotinja, zivotinja 
                FROM zivotinje WHERE IDveterinar=$ID
        UNION ALL
        SELECT 
                ID AS IDsimptom, simptom 
                FROM simptomi WHERE IDveterinar=$ID
        UNION ALL
        SELECT
                ID AS IDterminza, terminza
                FROM termniniza WHERE IDveterinar=$ID

Upvotes: 1

Views: 3841

Answers (2)

Wasim
Wasim

Reputation: 1174

Please note that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.The following SQL query should work. Please try it.

Edit: If the corresponding columns doesn't have same datatype, you can convert them into the same datatype as follows:

 SELECT 
        ID AS IDzivotinja, CAST(zivotinja AS CHAR) AS Col2
        FROM zivotinje WHERE IDveterinar=$ID
UNION ALL
SELECT 
        ID AS IDzivotinja, CAST(simptom  AS CHAR) AS Col2
        FROM simptomi WHERE IDveterinar=$ID
UNION ALL
SELECT
        ID AS IDzivotinja, CAST(terminza AS CHAR) AS Col2
        FROM termniniza WHERE IDveterinar=$ID

Upvotes: 1

John Woo
John Woo

Reputation: 263893

Columns must match with each other.

SELECT  ID, zivotinja as ColName, 'zivotinje' tableName
FROM    zivotinje 
WHERE   IDveterinar=$ID
UNION ALL
SELECT  ID, simptom as ColName, 'simptomi' tableName
FROM    simptomi 
WHERE   IDveterinar=$ID
UNION ALL
SELECT  ID, terminza as ColName, 'termniniza' tableName
FROM    termniniza 
WHERE   IDveterinar=$ID

one more thing, this is only a guess.

If columns zivotinja , simptom , terminza are just user define values (and the purpose of it is use to define where is the table that the value came from) then you need to wrap it with single quotes. 

SELECT  ID, 'zivotinja' as ColName
FROM    zivotinje 
WHERE   IDveterinar=$ID
UNION ALL
SELECT  ID, 'simptom' as ColName
FROM    simptomi 
WHERE   IDveterinar=$ID
UNION ALL
SELECT  ID, 'terminza' as ColName
FROM    termniniza 
WHERE   IDveterinar=$ID

Upvotes: 1

Related Questions