SQL query output

Databases
1

Just have a question on how to return some values in a query.

The table structure is

lodges
LodgeID (PK)
Lodge
etc

e.g.
1, London Lodge

lodge_activities_it
LodgeID
ActivityID

e.g.
1, 1
1, 2

lodge_activities
ActivityID (PK)
Activity
CategoryID

e.g.
1, Walking, 1
2, Indoor football, 2

Any given lodge will have just one main activity from each category, so I can output some results that include the Activity in a given category using:

SELECT 
 lodges.Lodge
 , lodge_activities.Activity
FROM lodges 
INNER JOIN lodge_activities_it ON lodges.LodgeID = lodge_activities_it.LodgeID 
INNER JOIN lodge_activities ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
WHERE lodge_activities.Category_ID = 1

So if, for example, Walking is an Activity in Category ID = 1, the above will give me:

Lodge, Category1 Activity
London Lodge, Walking

What I don’t know how to do is return activities from multiple categories in the same row. So for example, how could I return:

Lodge, Category1 Activity, Category2 Activity
London Lodge, Walking, Indoor Football

Thanks for any help with this.

2

Haven’t we done this same query multiple times?

You need to use GROUP_CONCAT

1 Like
3

Sorry Dave - you’re quite right. Can’t keep track sometimes.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.