I have 3 tables called:
- Applications (id, name)
- Resources (id, name)
- ApplicationsResources (id, app_id, resource_id)
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?