Welcome to WebmasterWorld Guest from 54.167.155.147

Forum Moderators: open

Message Too Old, No Replies

Identify source table name when using UNION

I'm using UNION too query 3 tables but need to identify records table.

   
11:20 am on Feb 28, 2008 (gmt 0)

5+ Year Member



I'm running the query below:

$query
="(SELECT id, col2, col3 FROM table1 WHERE user_id = $_SESSION[user_id])
UNION ALL
(SELECT id, col2, col3 FROM table2 WHERE user_id = $_SESSION[user_id])
UNION ALL
(SELECT id, col2, col3 FROM table3 WHERE user_id = $_SESSION[user_id])";

The problem is that I need to be able to identify which table each result originated from.

The only way I can figure out is to do it as 3 separate queries and then add a 4th column to the query result array with the name of the table in it and then later merge that array with the other two.

Seems too messy and I'm sure some of you clever guys have got a much better way!

I hope you can help me. Thanks in advance.

peter

1:01 pm on Feb 28, 2008 (gmt 0)

10+ Year Member




SELECT id, col2, col3, 'table1' AS source
FROM table1 WHERE user_id = $_SESSION[user_id])
UNION ALL
(SELECT id, col2, col3, 'table2'
FROM table2 WHERE user_id = $_SESSION[user_id])
UNION ALL
(SELECT id, col2, col3, 'table3'
FROM table3 WHERE user_id = $_SESSION[user_id]
2:02 pm on Feb 28, 2008 (gmt 0)

5+ Year Member



cool!

Thanks buddy!