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
- 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
- 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
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.comNote 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