differentiate rows in a union table

Question

Hi
I m selecting data from two different tables with no matching columns using this sql query

      select * from (SELECT s.shout_id, s.user_id, s.time FROM shouts s
union all
select v.post_id, v.sender_user_id, v.time from  void_post v) 
as derived_table order by time desc;

Now is there any other way or with this sql statement only can i differentiate the data from the two tables.

I was thinking of a dummy row that can be created at run-time(in the select statement only ) which would flag the row from the either tables.

As there is no way i can differentiate the shout_id that is thrown in the unioned table is shout_id from the shout table or from the void_post table.

Thanks
Pradyut

Answer 1

A dummy variable is a nice way to do it. There isn't much overhead in the grand scheme of things.

p.s., the dummy variable represents a column and not a row.

Answer 2

Sure, just add a new field in your select statement called something like source with a different constant value for each source.

SELECT s.shout_id, s.user_id, s.time, 'shouts' as source FROM shouts s 
UNION ALL
SELECT v.post_id, v.sender_user_id, v.time, 'void_post' as source FROM void_post v

Answer 3

You can just include an extra column in each select (I'd suggest a BIT)

select * from 
(SELECT s.shout_id, s.user_id, s.time, 1 AS FromShouts FROM shouts s
union all
select v.post_id, v.sender_user_id, v.time, 0 AS FromShouts from  void_post v) 
as derived_table order by time desc;