CODEWITHSUNDEEP
mysql
fir3x
fir3x

Reputation: 157

Join multiple tables and show the current table

What I'm trying to achieve maybe hard to explain so I will try my best.

I would like to join the results of three tables in mysql and in the results include the table name/or alias so i know which table the information has come from.

All my tables have common columns.

Upvotes: 1

Views: 433

Answers (3)

pechenie
pechenie

Reputation: 1958

Didn't you mean something like this?

SELECT t1.*, "t1" as tablename FROM t1
UNION 
SELECT t2.*, "t2" as tablename FROM t2

where t1 and t2 is your tables

By the way: it is not very correct to store data in such way. If you have necessity to union data from both tables in a single query, you should think of merging your tables in one with adding more valueable attribute as a new column (and probably add it to the key). For example, your tables are

CREATE TABLE american_cars(id INT, company TEXT, model TEXT)
CREATE TABLE european_cars(id INT, company TEXT, model TEXT)

then you should merge it as

CREATE TABLE cars(id INT, company TEXT, model TEXT, region TEXT)

or something like that.

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86386

You can use alias feature of mysql

like this

select table1.col1 as table1_col1, table2.col1 as table2_col1...

Upvotes: 1

Adrian Serafin
Adrian Serafin

Reputation: 7715

You should try:

select 'mytable1', column1, column2
union
select 'mytable2', column1, column2
union
select 'mytable3', column1, column2

Upvotes: 1

Related Questions