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.comHP 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.comObviously 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
) t1Resulting 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.comI 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
Hi rseales,
Sure, this article is just showing how to use the UNION ALL to combine the content of two tables. The aliases are what makes the SQL statement think two different columns in different tables are the same thing.
Using the examples above:
[code]
SELECT
title,
intro
FROM
(
SELECT
`title`,
`introtext` intro
FROM
`jos_content` a
WHERE
a.state=1
) t1
[/code]
and another table:
[code]
SELECT
title,
intro
FROM
(
SELECT
`name` AS title,
`description` AS intro
FROM
`jos_hp_items` b
WHERE
b.published=1
) t1
[/code]
If I run either of these statements, they both create a table with the two columns (title, intro):
[code]
-- Yields:
title intro
========== ============
content1.. content2
content3.. content4
[/code]
That may not be a great example. If you have examples of two tables you want to merge (just put demo content), then I may be able to help more.
like I will be to fill one new table created in joomla for other new JOOMLA extension, with DATA of others Tables of JOOMLA property of others JOOMLA extensions? i.e please. help me with this example, please.
joel, excuseme, is possible to create other(s) table(s) taking data of tables differentes within of joomla (or PHP/MySQL DATABASES, IS NOT NEEDLY JOOMLA DATABASE) with differentes labels and destinations tables to have others labels, but with the merge of data fields? code examples, please. HELPME with this, please.
JOEL, IS POSSIBLE modified this code for to crete other(s) table(s) of others tables of two DATABASES independients or differents? code? parameters? Thanks.