About Union select

[woonm]

Hi there:

Thank you all for the helps! I just have another question about the use of Table name as the field of "Union select" to describe the data sources. The SQL describes as such:

-------------------------------------
SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;
--------------------------------------
And it does not take the "Customers" or "Supplies" as the name to insert into a new field called "Relationship".

Is there a solution for this?

Thanks in advance

 

[swampBoogie]

You shouldn't use [] in SQL. That's a Microsoft invention.


SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
ORDER BY City, CompanyName;

 

[woonm]

Thanks for prompt response. I tried the query at the mySQL prompt line.

It returned Error 1064 at line 3.

I did check the mySQL manual and it lists on the version 4. I am runing version 3.23.54 on RedHat Linux.

Is that the reason why it did not work?

Thanks.

 

[swampBoogie]

Yes, union is only supported in version 4 onwards.

 

[woonm]

Thank you for the prompt help. Really help me a lot.