Identifying source table from UNION query

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.

Should be easy enough, just do something like this:

SELECT Title, 1 FROM table1
UNION ALL
SELECT Title, 2 FROM table2
UNION ALL
SELECT Title, 3 FROM table3