plz help - join query

[viji]

hi,
select r.roomid from hotel_message_setdetails
s,hotel_message_roomset r where s.setname=@sname and
s.setid=r.setid

this query will give the 3 rows result like:
223,224,225


select top 3 l.clientid,a.appname,time from log
l,logmsg lm,apps a where clientid=@pc and
l.logmsgid=lm.logmsgid and a.appid=lm.appid order by
time desc

this one will give:
223 VOD 2005-10-28 18:11:16.000
223 VOD 2005-10-28 18:10:42.000
223 Music 2005-10-28 18:09:57.000
224 TV 2005-10-28 18:09:17.000
224 Music 2005-10-28 18:08:14.000
224 Neos 2005-10-28 18:08:13.000
225 VOD 2005-10-28 13:03:55.000
225 VOD 2005-10-28 12:41:22.000
225 VOD 2005-10-28 11:44:35.000


how to combine both queries...with out using cursor...

if anyone knows plz help me...

thanx,
viji.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[viji]

hi friends,
select l.clientid as roomno,a.appname,time from log
l,logmsg lm,apps a,hotel_message_setdetails s,
hotel_message_roomset r where s.setname='test' and
s.setid=r.setid and
r.roomid=l.clientid and l.logmsgid=lm.logmsgid and
lm.appid=a.appid order by time desc

for this query i got output.but if i ll use top then i
can get only 3 rows of roomno 223.but i want all the
rooms.for that what to do.

thanx,
viji.

[viji]

hi friends,
i did like this.
declare @roomTable table (Rid int identity, roomId
nvarchar(20))
declare @results table (Rid int identity, roomId
nvarchar(20),appname nvarchar(20),time datetime)
insert @roomTable

select r.roomid from hotel_message_setdetails
s,hotel_message_roomset r where s.setname=@sname and
s.setid=r.setid

select @pc = roomId from @roomTable
-- while (@pc is not null)
while EXISTS (select * from @roomTable)
begin
insert @results (roomid,appname,time) select top 3

l.clientid,a.appname,time from log l,logmsg lm,apps a
where clientid=@pc and l.logmsgid=lm.logmsgid and
a.appid=lm.appid order by time desc

delete @roomTable where roomId = @pc
select @pc = roomId from @roomTable
end
delete @roomTable
select roomid as RoomNumber,appname as
ApplicationUsed,time as Time from @results order by
roomid
delete @results

regards,
viji

__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

[Siva CH]

Hi Vijii,

If you have a proper key columns that are common to the tables between query1 and query2, then we can have a meaningful mapping between them, so that we can combine both the queries to get the required result.

 

Thanks,

Siva