table join /query help

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.

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

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

Thanks! I will work with that. I need to get my head around JOIN ...
at the moment i am using this:

SELECT
activity.activity_id, activity_title, activity_photo,
activity_short_desc, activity_category.category_id,
activity_country.country_id
FROM
activity, activity_category, activity_country
WHERE
activity_category.category_id ='5' // or whatever
AND activity_country.country_id = '$country_id'
AND activity.activity_id = activity_country.activity_id
AND activity.activity_id = activity_category.activity_id

At least for the moment it is working. But I'm not sure it's the
correct (or most efficient) way to go about things, and I am having an
impossible time creating similar queries. Is the JOIN method more
efficient? Thanks again.

On Apr 22, 4:15 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 22 Apr 2008 10:43:22 +0200, Captain Paralytic
>
>
>
> <paul_laut...@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

On 23 Apr, 02:12, cruiserweight <bayo...@yahoo.com> wrote:
> On Apr 22, 4:15 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>
> > On Tue, 22 Apr 2008 10:43:22 +0200, Captain Paralytic
>
> > <paul_laut...@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
> Thanks! I will work with that. I need to get my head around JOIN ...
> at the moment i am using this:
>
> SELECT
> activity.activity_id, activity_title, activity_photo,
> activity_short_desc, activity_category.category_id,
> activity_country.country_id
> FROM
> activity, activity_category, activity_country
> WHERE
> activity_category.category_id ='5' // or whatever
> AND activity_country.country_id = '$country_id'
> AND activity.activity_id = activity_country.activity_id
> AND activity.activity_id = activity_category.activity_id
>
> At least for the moment it is working. But I'm not sure it's the
> correct (or most efficient) way to go about things, and I am having an
> impossible time creating similar queries. Is the JOIN method more
> efficient? Thanks again.
>
Please do not top post (top posting fixed).
Using explicit JOINs it not necessarily more efficient, but it is
clearer and much less troublesome as implicit comma joins are weighted
differently.

On Apr 23, 5:03 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 23 Apr, 02:12, cruiserweight <bayo...@yahoo.com> wrote:
>
> > On Apr 22, 4:15 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>
> > > On Tue, 22 Apr 2008 10:43:22 +0200, Captain Paralytic
>
> > > <paul_laut...@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
> > Thanks! I will work with that. I need to get my head around JOIN ...
> > at the moment i am using this:
>
> > SELECT
> > activity.activity_id, activity_title, activity_photo,
> > activity_short_desc, activity_category.category_id,
> > activity_country.country_id
> > FROM
> > activity, activity_category, activity_country
> > WHERE
> > activity_category.category_id ='5' // or whatever
> > AND activity_country.country_id = '$country_id'
> > AND activity.activity_id = activity_country.activity_id
> > AND activity.activity_id = activity_category.activity_id
>
> > At least for the moment it is working. But I'm not sure it's the
> > correct (or most efficient) way to go about things, and I am having an
> > impossible time creating similar queries. Is the JOIN method more
> > efficient? Thanks again.
>
> Please do not top post (top posting fixed).
> Using explicit JOINs it not necessarily more efficient, but it is
> clearer and much less troublesome as implicit comma joins are weighted
> differently.

Sorry about top posing. I will study up on joins. Thank you.