Merge the content of two similar tables in MySQL

: Joel Lipman; MySQL; 11 January 2011; Hits: 14404

In standard SQL we can combine the contents of two tables with a CROSS JOIN, (BTW these are not instructions to create some table I'll never use again unlike the rest of the solutions I found on the web). In MySQL, I only know how to do this with a UNION ALL clause.

Scenario:
I have a Joomla module which should take the keywords from a specified number of different but similar tables. I'm looking to query the Title and the Introductions of any valid articles from BOTH tables and return one table with everything I want.

Consider the following two tables exist:

Joomla Articles (table name: jos_content):

copy raw

id      title      introtext
--------------------------------------------------------------------------
1       Welcome    This is Joomla
2       Copyright  This site and its design is property of JoelLipman.com

id title introtext
--------------------------------------------------------------------------
1 Welcome This is Joomla
2 Copyright This site and its design is property of JoelLipman.com

HP Articles (table name: jos_hp_items):

copy raw

id      name       description
--------------------------------------------------------------------------
1       Welcome    This is HP
2       Copyright  This site and its design is property of SomeoneElse.com

id name description
--------------------------------------------------------------------------
1 Welcome This is HP
2 Copyright This site and its design is property of SomeoneElse.com

Obviously the two above tables are very similar with different column names. I also don't want the data to produce double the number of columns.

My Solution

copy raw

title        intro
--------------------------------------------------------------------
Welcome      This is Joomla
Copyright    This site and its design is property of JoelLipman.com
Welcome      This is HP
Copyright    This site and its design is property of SomeoneElse.com

title intro
--------------------------------------------------------------------
Welcome This is Joomla
Copyright This site and its design is property of JoelLipman.com
Welcome This is HP
Copyright This site and its design is property of SomeoneElse.com

Resulting in:

title        intro
--------------------------------------------------------------------
Welcome      This is Joomla
Copyright    This site and its design is property of JoelLipman.com
Welcome      This is HP
Copyright    This site and its design is property of SomeoneElse.com

Note how I don't use the `id` columns of the tables as I'm quite content with just the two columns I requested. And for those of you that don't know, the alias "AS" is optional in MySQL (think it's optional for most SQL forms).

I found that to add further tables, simply start with another "UNION ALL" clause with a note to give the 3rd table a different alias to the 2nd (so "C" instead of "B"):

SELECT
	title,
	intro
FROM
	(
		SELECT
			`title`,
			`introtext` intro
		FROM
			`jos_content` a
		WHERE
			a.state=1
		UNION ALL 
			SELECT 
				`name` AS title,
				`description` AS intro 
			FROM 
				`jos_hp_items` b 
			WHERE 
				b.published=1
		UNION ALL 
			SELECT 
				`title`,
				`description` intro 
			FROM 
				`jos_weblinks` c
			WHERE 
				c.published=1
	) t1

Category: MySQL :: Article: 316

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

SQL: Use CASE for Relevance column (3819) Joel Lipman (MySQL)
What? So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a...
Trim values along with the tab (2185) Joel Lipman (MySQL)
What? A quick article on how to trim in MySQL along with getting rid of any leading or trailing tab characters. Why? I use MS Excel...
Error 1065: Query was Empty (7148) Joel Lipman (MySQL)
What? In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong...
MySQL: Find all non-alphanumeric rows (12243) Joel Lipman (MySQL)
What? Quick note on how to do this. I was tasked with cleaning up an english database by replacing all special...
Quickly update all content in mySQL database replacing a string (5170) Joel Lipman (MySQL)
What? This is an article on how to replace a string with another in all your joomla articles without modifying other...
Merge the content of two similar tables in MySQL (14404) Joel Lipman (MySQL)
In standard SQL we can combine the contents of two tables with a CROSS JOIN, (BTW these are not instructions to create some...
MySQL Transactions in PHP (3796) Ike Francis (MySQL)
Sometimes it is critical to ensure a group of queries are all executed successfully to maintain the integrity of our...