CODEWITHSUNDEEP
mysqlsqldatabaseunionunion-all
ikiK
ikiK

Reputation: 6532

Mysql UNION way to differ from which table result row is

Lets say I have below query. 

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

Lest say there is one same city NY in both tables witch will give me: 

| City     |
| -------- | 
| NY       |
| NY       |

I am fetching this in a HTML table, is there a way to differ from witch MySQL table result row is from, other then making another column in tables itself, for example column type.

Maybe is possible to append something to result set itself in query?

Upvotes: 0

Views: 559

Answers (5)

stoos
stoos

Reputation: 55

if you add source identifier column it does not matter if you use UNION or UNION ALL because all cross table rows will be "unique". That may lead to duplicates. It depends if that is wanted behavior or not. If you want to remove duplicates you would need to group your results like this:

SELECT City, MIN(SourceId) FROM (
SELECT 1 as SourceId, City FROM Customers
UNION ALL
SELECT 2 as SourceId, City FROM Suppliers
)
GROUP BY City
ORDER BY City

By setting the SourceId value you are able to control from which table duplicates are removed. In the example above duplicates are removed from the Suppliers table.

Upvotes: 0

zealous
zealous

Reputation: 7503

you can add column in your query like following.

SELECT city, 'c' as type FROM Customers
UNION
SELECT city, 's' as type FROM Suppliers
ORDER BY city, type;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I don't understand your results, because union removes duplicates, so it should produce only one row.

If you want to include the table name, I would advise using select distinct and union all:

SELECT DISTINCT 'customers' as type, City FROM Customers
UNION ALL
SELECT DISTINCT 'suppliers' as type, City FROM Suppliers
ORDER BY City;

Upvotes: 2

Maverick
Maverick

Reputation: 1599

Append an extra column with the literal string value as the table name along with the existing query. This will help you to distinguish the rows.

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

Update: UNION removes the duplicate rows. So, if your tables have same values in the City column, it will return the unique rows. You have to use UNION ALL. This will return all rows and appending the string literal as the type column will help you to distinguish between the rows.

Thanks @Gordon for pointing it out.

Upvotes: 2

Barmar
Barmar

Reputation: 781098

Add an extra column containing a literal value depending on which subquery it is.

SELECT 'Customers' AS type, City
FROM Customers
UNION
SELECT 'Suppliers' AS type, City
FROM Suppliers
ORDER BY City, type

Upvotes: 0

Related Questions