Select separate rows from two tables, order by date
I don't want to any kind of JOIN
here. I'm building an RSS feed of two tables using PHP, and I want to select all the rows from the two tables, keeping the rows separate but sorting by a common created
column.
For example, if I have a table foo
:
id downloads views created
-----------------------------------------------
1 12 23 2011-07-22 00:10:16
2 51 900 2011-07-22 10:11:45
3 8 80 开发者_JS百科2011-07-23 04:12:18
And a table bar
:
id title body created
-----------------------------------------------
1 foo ogblog 2011-07-21 10:54:07
3 bar zip 2011-07-24 10:54:07
4 zip bar 2011-07-25 10:54:07
I want to select all data from both tables ordered by the common created
column, so an example resultset would be (ignoring bar.id
as it's not needed):
id title body downloads views created | table
-------------------------------------------------------------------------------
NULL bar zip NULL NULL 2011-07-24 10:54:07 | bar
NULL foo ogblog NULL NULL 2011-07-21 10:54:07 | bar
1 NULL NULL 12 23 2011-07-22 00:10:16 | foo
2 NULL NULL 51 900 2011-07-22 10:11:45 | foo
3 NULL NULL 8 80 2011-07-23 04:12:18 | foo
NULL zip bar NULL NULL 2011-07-25 10:54:07 | bar
The table
column isn't needed; I added it to make things a little easier to understand.
Hopefully it's obvious what I want to do; instead of doing a JOIN
where a row is generated from columns from two tables, I want to get all the row data with a common column layout where any column that doesn't exist in a table has NULL
put into it.
Please let me know if you need clarification.
Using dummy columns to account for the different structures, a union to join them and a parent select to handle the ordering:
SELECT * FROM (
(SELECT foo.id, NULL AS title, NULL AS body, foo.downloads, foo.views, foo.created FROM foo)
UNION ALL
(SELECT NULL AS id, bar.title, bar.body, NULL AS downloads, NULL AS views, bar.created FROM bar)
) results
ORDER BY created ASC
Expanding on @Brendan Bullen's suggestion, here's an example using UNION ALL
that should work for you:
SELECT id as id, NULL as title, NULL as body, downloads as downloads,
views as views, created as created, 'foo' as table_name
FROM foo
UNION ALL
SELECT NULL as id, title as title, body as body, NULL as downloads,
NULL as views, created as created, 'bar' as table_name
FROM bar
ORDER BY created ASC
精彩评论