Help with SQL query

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.

1 Like

i don’t see any relationship between continent and country, so the answer is no

You need a little change in your database table structure to get the expected output.

Thanks - I was sleeping on it, and thinking I probably need to separate out countries and continents. I was able to get it working if just one continent was selected, but not multiple continents. So thanks for confirming that.

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