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.