can you have a subquery that returns multiple values???

Hey all,
I have 3 tables:
RolesTbl with the columns  RoleID, RoleName, RoleDesc etc..
ApplicationsTbl with the columns ApplicationID, ApplicationName etc...
ApplicationRoleLinkTbl with the columns ApplicationID, RoleID
So a role is linked to an application through the ApplicationRoleLinkTbl.

I want to show all roles and also shows the name of the application the role belongs to:
The sql is:

select  rt.RoleID, rt.RoleName, rt.RoleDesc,
(select ap.ApplicationName from ApplicationsTbl ap left outer join ApplicationRoleLinkTbl apl
on ap.ApplicationID = apl.ApplicationID
where apl.RoleID = rt.RoleID) as applicationName
from RolesTbl rt

This is fine until I associate more than one application with the role.
In this case it I get an error saying Subquery returned more than 1 value.
A role can only be associated with a max of 2 applications.
Can i somehow make a single query that will show the 2 apps associated with a role?

Thanks
O

osionAsked:
Who is Participating?
 
chapmandewCommented:
select  rt.RoleID, rt.RoleName, rt.RoleDesc,
(select ap.ApplicationName + ',' from ApplicationsTbl ap left outer join ApplicationRoleLinkTbl apl
on ap.ApplicationID = apl.ApplicationID
where apl.RoleID = rt.RoleID for xml path('')) as applicationName
from RolesTbl rt
0
 
osionAuthor Commented:
wow! that was fast!
:)
thanks a million. Had a quick look at for xml path('')
how does that work here. is it just appending things?
thanks again!
0
 
chapmandewCommented:
yeah, it just takes the subset of data, and appends it to one string.  its really neat stuff..
0
 
osionAuthor Commented:
Thanks so much!! really fast answer. Was going round in circles with that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.