Hosam alzagh Hosam alzagh - 7 months ago 7
SQL Question

How to select table names with UNION results?

Here are the results with UNION, but I also want to know the source table for each result:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;


The actual result is this:


city

572

123


But I'd like this result instead:


city tablet_result

572 Customers

123 Suppliers

Answer

Ok, try:

SELECT City,'Customers' as tblName FROM Customers
UNION
SELECT City,'Suppliers' as tblName FROM Suppliers
ORDER BY City;

Play with the order by.

Also consider doing a UNION ALL. See this for the difference.

Comments