Mike Mike - 4 months ago 5
SQL Question

is it view or join what I need for this mysql exersice

I came across small task using mysql and mphpmyadmin, which I need to combine 2 tables records in one table, after each other... but also want the results to indicate the table that comes from, something like this:

table1: table name: "names1"

id code name number
1 XA Mike 101
2 DA Stel 344
3 MB Dan 434
4 TR Tina 321


table2: table name: "names2"

id code name number
1 DC Man 121
2 QZ Dan 989
3 VN Was 912


What I need is to combine them in one table, select, view... any statement to use in my script... something like this:

tablename code name number
names1 XA Mike 101
names1 DA Stel 344
names1 MB Dan 434
names1 TR Tina 321
names2 DC Man 121
names2 QZ Dan 989
names2 VN Was 912


Tried join and union, failed, do you advice to create a view? and how you would suggest to be done?

Answer
select  code, name,  number, 'names1' as tablename from table1
union
select   code, name,  number, 'names2' as tablename from table2;

also look into the difference between union vs union all as it relates to dupes (in general for your other union like queries)

Comments