Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Identifying source table from UNION query

Tags:

sql

union

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.

like image 594
Dan Avatar asked Sep 10 '09 12:09

Dan


People also ask

How do I find the source table of a view in SQL Server?

To find all of the SQL Server database views where a table is used, just apply a filter criteria on table_name column of the information schema view INFORMATION_SCHEMA. VIEW_TABLE_USAGE as seen below.

How do I print a table name in SELECT query?

The syntax to get all table names with the help of SELECT statement. mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema. tables where table_schema = 'test'; Output with the name of the three tables.


2 Answers

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.

like image 54
paxdiablo Avatar answered Oct 19 '22 04:10

paxdiablo


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
like image 35
Eric Petroelje Avatar answered Oct 19 '22 02:10

Eric Petroelje