CODEWITHSUNDEEP
mysqljoin
AgelessEssence
AgelessEssence

Reputation: 6731

MySQl, join/merge 2 tables without shared column and ordering by a common column

I have two tables with identical structures handling distinct data. I want to merge them, add a text field indicating where the data for that row came from, and order by a common field.

TABLE1

ID|NAME|YEAR  

1,'peter',2008
2,'edward',2010

TABLE2

ID|NAME|YEAR

1,'compadre',2009
2,'vika',2011

DRAFT of query ( obviously is erroneous )

select * from TABLE1 JOIN TABLE2 order by YEAR asc

expected result:

1,'peter','iamfromTABLE1',2008
1,'compadre','iamfromTABLE2',2009
2,'edward','iamfromTABLE1',2010
2,'vika','iamfromTABLE2',2011

I know I can do this using PHP/MySQL, but is there not a more elegant way like the "One Simple Query".

Upvotes: 2

Views: 1066

Answers (1)

MPelletier
MPelletier

Reputation: 16687

Use a Union query and literals:

SELECT ID, Name, 'iamfromTABLE1' as indicator, Year
FROM Table1
UNION
SELECT ID, Name, 'iamfromTABLE2' as indicator, Year
FROM Table2
ORDER BY Year

EDIT: as indicator added on recommendation of iim.hlk

Upvotes: 6

Related Questions