Not sure if this is possible - I have the following tables and records:
lodge_keyword_categories
CategoryID (PK)
Category
14, Continent
15, Country
lodge_keywords
KeywordID (PK)
CategoryID
Keyword
1, 14, North America
2, 14, Africa
3, 14, Europe
4, 15, USA
5, 15, Canada
6, 15, Mexico
7, 15, Kenya
8, 15, Tanzania
9, 15, South Africa
10, 15, UK
11, 15, France
12, 15, Germany
user_keywords_it
KeywordID
CategoryID
UserID
1, 14, 101
2, 14, 101
So in this case UserID 101 has been tagged with the keywords 1 (North America) and 2 (Africa).
From that is it possible to return only the countries in North America and Africa, i.e. keywords 4, 5, 6, 7, 8 and 9?
The use case for all of this is a couple of pages where a user first selects some continents, and then selects some countries. But ideally only be presented with the countries in the continents they have ticked first.
Hope that makes sense - any pointers much appreciated.
Thank you.