uese cte twice in a query

Angela4eva
Angela4eva used Ask the Experts™
on
I trying to use a cte twice in same query and i am getting error in second selecte statement.
if the comment the firts select it works. How can i use same cte twice in my query? please help
;with cte as
(
select * from  c
inner join  r on
r.rid = c.id
where r.fid = 11111)

select * from  s  
inner join cte on
cte.rid=s.rid

select * from o  
inner join cte on
cte.roomid=o.rid

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
use
with cte as
(select * from  c
inner join  r on
r.rid = c.id
where r.fid = 11111)

select * from  s  
inner join cte on
cte.rid=s.rid

with cte as
(select * from  c
inner join  r on
r.rid = c.id
where r.fid = 11111)

select * from o  
inner join cte on
cte.roomid=o.rid

Open in new window

Sr. System Analyst
Commented:
http://msdn.microsoft.com/en-us/library/ms175972.aspx

A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial