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?