Identify record table from union all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identify record table from union all

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-17 : 06:56:16

Hi all,

I have a union all statement that displays a set of records as links when I click on the link I want it to take it to another page which is dependant on which table the record came from. Is there anyway I can do this apart from passing the id of the record? which could overlap but is unlikely with the data concerned?

here is the union statement I'm using classic asp and sql server 2005:


select top 7 * from (
select departuredate = convert(varchar, departuredate, 1),departureairportname,duration,country,resort,boardshort,price,pricebasedon,id,rating from vholiday
where bfrontpage = 1
union all
select departuredate = convert(varchar, departuredate, 1),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating from vcitybreaks
where bfrontpage = 1
union all
select departuredate = convert(varchar, availablefrom, 1),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating from vluxury
where bfrontpage = 1 )
as u1
order by 1 desc

thanks in advance

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:59:42

Is this?

select top 7 * from (
select departuredate = convert(varchar, departuredate, 1),departureairportname,duration,country,resort,boardshort,price,pricebasedon,id,rating,'vholiday' as table_name from vholiday
where bfrontpage = 1
union all
select departuredate = convert(varchar, departuredate, 1),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating,'vcitybreaks' as table_name from vcitybreaks
where bfrontpage = 1
union all
select departuredate = convert(varchar, availablefrom, 1),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating,'vluxury' as table_name from vluxury
where bfrontpage = 1 )
as u1
order by 1 desc

Madhivanan

Failing to plan is Planning to fail

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-17 : 07:07:33

thanks madhivanan, I think I will just pass that to a conditional statement that changes the URL of the link depending on the table name.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-17 : 11:11:02

actually I just put the URL in the SQL statement, saved me using another conditional statement in the code.

thanks

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-18 : 03:24:25

quote:
Originally posted by Cowboy

actually I just put the URL in the SQL statement, saved me using another conditional statement in the code.

thanks

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Good

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources