I have 3 tables called:
I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource.
So the question is, what is the best way to do this in SQL as I need to get all resources and I also need to get all applications for each resource?
Do I run a select * from resources first and then loop through each resource and do a separate query per resource to get the list of applications for that resource?
Is there a way I can do this in one query?
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
I recommend reading about string aggregation/concatentation in Oracle.