Help with SQL query - COUNT

Databases
1

Hope someone can help with this, as its a bit beyond me I think. Its to do with the COUNT function, and at least involves a fairly simple database structure, even if its proving tricky to do what I would like.

The tables I have are:

lodges
LodgeID (PK)
Lodge

Sample data:

1, Lodge A
2, Lodge B

lodge_activities_it
KeywordID (PK)
ActivityID
LodgeID

Sample data:

1, 11, 1
2, 12, 1
3, 13, 1
4, 14, 1
5, 15, 1
6, 16, 2
7, 17, 2
8, 18, 2
9, 19, 2
10, 20, 2

user_activities_it

KeywordID (PK)
ActivityID
UserID

Sample date:

1, 11, 1
2, 12, 1
3, 21, 2
4, 13, 2
5, 15, 3
6, 17, 3
7, 18, 4
8, 19, 4
9, 20, 5
10, 25, 5

From all of that I would love to have the output which just displays the LodgeID, Lodge and number of matching activities that lodge has with users.

So from the above data, Lodge A shares activities with User 1 (Activities 11 & 12) and User 2 (Activity 13).

And Lodge B shares activities with User 3 (Activity 17), User 4 (Activities 18 and 19) and User 5 (Activity 20).

So from all of that, the output I would be looking for is just:

1, Lodge A, 2
2, Lodge B, 3

Where the last column is the number of users which match any of that lodge’s activities.

Sorry its a bit wordy, but hopefully it makes some sense

Many thanks.

2

All you need to do is join lodges and lodge_activities_it, then use the COUNT function, using GROUP BY of the lodgeID (you’ll need to specify which table this is from since it’s on both tables) and Lodge

3

Thanks Dave - won’t that just give me the number of activities for each lodge?

(Rather than the number of users which have any of the same activities as each lodge?)

4

Oops. Yes, you’re right. So then you need to also join the user_activities_id and then COUNT the userID instead. The group by will stay the same.

5

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