Search code examples
sqlunion

Identifying source table from UNION query

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.

Solution

  • 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