stuff function not working with left or right join

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

stuff function not working with left or right join

Author

Topic

summer12in
Starting Member

6 Posts

Posted - 2015-01-01 : 23:04:22

I found something very strange...stufff function working with self join but not working with left or right join,.
I have a table

**Id name**

1 samar

1 Harry

2 jack

I want the output as

**Id name**

1 samar Harry

2 jack

The below query works fine with self join

Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)

From #test b

Group by id

But when i do right join i get error _ invalid object name `b`. Please let me know where i am going wrong....

Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)

From #test b

Group by id

Samarjeet

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-01 : 23:32:02

Select b.id,
stuff ((select ` ` + a.name from #test a
right join #test b on b.id = a.id for xml path (``),1,1,``)
Group by id

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95