?
Solved

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

Posted on 2009-04-29
4
Medium Priority
?
215 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:osion
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 24260094
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
 

Author Comment

by:osion
ID: 24260180
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1000 total points
ID: 24260193
yeah, it just takes the subset of data, and appends it to one string.  its really neat stuff..
0
 

Author Closing Comment

by:osion
ID: 31575930
Thanks so much!! really fast answer. Was going round in circles with that!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question