Sorting and Merging data from three different tables in an efficient way

Question

Consider the following three tables:

t1(id,name,cDate,foo,…)
t2(id,name,cDate,bar,…)
t3(id,name,cDate,other,…)

The tables represent some independent entities. I need to display them on site homepage regardless of their type in a list sorted by creation date (cDate).

rows
---
t1
t2
t2
t3
t2
t1

Currently I have created a view (or a subquery) by union of some shared columns and an auxilary column for table name: v1(id,cDate,tableName) to select them ordered by cDAte. After that I query on the each table to get tables' rows and merge the result in a PHP array and send back them to view. However I think it is not so efficient and clean. Is there any better approach?

The output be a ready-to-use resourse containing all data of the three tables.

Answer 1

As a improvement of the @John Green answers I created a SQL fiddle with the usage of the COALESCE function, it return the first not null argument:

SELECT COALESCE(t1.id, t2.id, t3.id) id
, COALESCE(t1.name, t2.name, t3.name) name
, COALESCE(t1.cDate, t2.cDate, t3.cDate) cDate
, COALESCE(t1.foo, t2.bar, t3.other) uniqueField
FROM (
    SELECT 't1' AS source, id FROM t1
    UNION
    SELECT 't2' AS source, id FROM t2
    UNION
    SELECT 't3' AS source, id FROM t3
) ad
LEFT JOIN t1 ON ad.id = t1.id AND ad.source = 't1'
LEFT JOIN t2 ON ad.id = t2.id AND ad.source = 't2'
LEFT JOIN t3 ON ad.id = t3.id AND ad.source = 't3'
ORDER BY cDate

The advantage of using this method is that you can combine the fields that are not common in all the tables the way your business rules require, as shown in the uniqueField