On Tue, 22 Apr 2008 10:43:22 +0200, Captain Paralytic
<paul_lautman@yahoo.com> wrote:

> On 22 Apr, 05:03, cruiserweight <bayo...@yahoo.com> wrote:
>> I am at the limits of my limited mysql knowledge. I have five tables:
>> activity, country, category, activity_category, activity_country.
>>
>> activity is a table of holiday activities, for example "camping in
>> yosemite" or "rock climbing in moab"; country is a list of countries
>> (1=usa, 2=mexico, 3=canada, 4=brazil, 5=italy, etc); category is a
>> list of activity types: camping, diving, rock climbing, cycling, etc.
>> activity_category is two fields, activity_id and category_id;
>> activity_country the same; activity_id and country_id.
>>
>> activities can have more than one country as well as more than one
>> category (a hiking and cycling trip though italy and france, for
>> example).
>>
>> What I am trying to select is activities by country by category, for
>> example, cycling trips in italy. I assume my tables are set up
>> correctly? I don't quite understand table joins, and i suspect that
>> this might be the answer to my problem. if someone could help explain,
>> or point me in the right direction to learn on my own, i would be
>> forever grateful. thanks very much in advance for your time and help.
>
> I don't see the need for the "activities" table.
> country, category, country_category
> would seem to serve the same purpose

Unless as I suspect activities are an integral part of his data and goal
to maintain. It's not the question wether or not a country has certain
activities, but wether there are certain (scheduled?) activities in that
country involving one of the categories. It could even be possible
activities cross borders and as the OP indicated an activity has several
categories.

For readabilities sake I'd split it into multiple queries (possibly
determenining category.id and country.id beforehand). However, it should
be possible in one query, allthough it seems less efficient:

SELECT
act.id, act.name
FROM countries cn
JOIN activity_country acn
ON acn.country_id = cn.id
JOIN activities act
ON act.id = acn.act_id
JOIN activity_category ac
ON ac.act_id = at.id
JOIN categories cat
ON cat.id = ac.cat_id
AND cat.name LIKE 'cycling'
WHERE cn.name LIKE 'Italy'
GROUP BY act.id


If you know the country.id & activity.id it becomes somewhat simpler:

SELECT act.id, act.name
FROM activity_country acn
JOIN activities act
ON act.id = acn.act_id
JOIN activity_category ac
ON ac.act_id = at.id
AND ac.id = <activity.id>
WHERE acn.country_id = <country.id>
--
Rik Wasmus