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):

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):

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

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
	) t1

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

< Prev		Next >