SQL - Identifying source table from UNION query

Question

I'm building an RSS feed in PHP which uses data from three separate tables. The tables all refer to pages within different areas of the site. The problem I have is trying to create the link fields within the XML. Without knowing which table each record has come from, I cannot create the correct link to it.

Is there a way to solve this problem? I tried using mysql_fetch_field, but it returned blank values for the tables.

$sql = "
SELECT Title FROM table1
UNION 
SELECT Title FROM table2
UNION 
SELECT Title FROM table3";

There are other fields involved, but this is basically the query I'm using.

Any help would be appreciated.

Thanks.

Answer 1

Just add a constant to your column list as follows:

select 'table1' as table_name, title from table1
union all
select 'table2' as table_name, title from table2
union all
select 'table3' as table_name, title from table3

which will get you something like:

table_name | title
-----------+-----------------------------
table1     | war and peace
table2     | 1984
table3     | terminator salvation

and so on.

This allows you to have string data types which will likely make your conversion to links easier (especially if you use values that just have to be copied to your page instead of being looked up or converted) and using the as clause will allow you to reference it like any other column (by name).

Note the use of union all - if you're sure that there will be no duplicate rows from the tables (which is probably true in this case since you have a different table_name value for each and I'm assuming the titles are unique), the union all can avoid a wasted sort-and-remove-duplicate operation. Use of union on its own may cause unnecessary work to be done.

If you want the duplicate removal done, just revert to using union.