Welcome to WebmasterWorld Guest from 23.20.184.141

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)

Junior Member

10+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


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)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0



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)

Junior Member

10+ Year Member

joined:Apr 20, 2006
posts: 56
votes: 0


cool!

Thanks buddy!