Identifying source table from UNION query

user_146989 0
Viewed 12143

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.

union sql
4 Answers

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

user_60555700

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.

user_4819270
Related