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.

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.

Allium

Answer 1

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

Sulfatize

Answer 2

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.

Mettah

Doing this could result in duplicate records regardless of whether you use union or union all. – Carbonari

Answer 3

the below query works better and gives you better idea as above queries don't render rows twice.

SELECT t1.*,t1.table_name
FROM
t1
UNION ALL
SELECT t2.*,t2.table_name
FROM
t2
LEFT OUTER JOIN 
t1
ON t1.id=t2.id
WHERE t1.id=null

the above script don't give duplicate data rows and make sure

1. All common data bteween t1 and t2 as source t1 table.
2. Non t2 rows source as t1 table .
3. Non t1 rows source as t2 table.

if you want common data from t2 table , flip left join

Lewan

Answer 4

Add a dummy column with a constant vale. Eg. "Select 'funky_table' as source_table, title from funky_table" for each clause but with different names in the quotes.

Athabaska

Answer 5

One more solution which removes duplicates using grouping without joins:

SELECT Title, MIN(TableName) AS TableName 
FROM(
    SELECT 'table1' as TableName, title from table1
    UNION ALL
    SELECT 'table2' as TableName, title from table2
    UNION ALL
    SELECT 'table3' as TableName, title from table3
) AS t
GROUP BY Title
ORDER BY TableName

fiddle here

Important: because of MIN function duplicates are removed according your source table identifier sorting. So you can prioritize your sources.

Ropedancer