[thelist] MySQL: How to see which table it was, after UNION

Tab Alleman Tab.Alleman at MetroGuide.com
Thu Jul 8 16:03:24 CDT 2004


Mike wrote:
> Hi group
> 
> Maybe someone can help with this query? I'd like to show the last
> entries of several DB tables on a homepage, ordered by date. So
> far no big deal with the new UNION command. But here it comes:

> (select id as homeId,title as homeTitle,teaser as homeTeaser,date as
> homeDate from muinar_webnews order by homeDate desc limit 1) union
> (select id as homeId,title as homeTitle,subtitle as homeTeaser,date as
> homeDate from kmu_tips order by homeDate desc limit 1)
> order by homeDate desc";


How about this:

(
select 
	'muinar_webnews' as tableName
,	id as homeId
,	title as homeTitle
,	teaser as homeTeaser
,	date as homeDate 
from muinar_webnews 
order by homeDate desc limit 1
) union (
select 
	'kmu_tips' as tableName
,	id as homeId
,	title as homeTitle
,	subtitle as homeTeaser
,	date as homeDate 
from kmu_tips 
order by homeDate desc limit 1
)
order by homeDate desc";

Then tableName in the resultset should have the correct value.

> $homeTable always shows the same value, i. e. the first of the two
> tables, which is wrong. What's wrong with using mysql_field_table
> here? Is there a better way to identify the tables?

No idea what mysql_field_table is supposed to be... I assume it's PHP
specific.


More information about the thelist mailing list