help with this join

Ok I have 3 tables, parks(id, name), activities(id,name) and contains(park_id, activity_id). The basic idea is parks contain activities. Now what I'm trying to so is get a list back of what parks contain specified activities. So what parks contain activities 1, 4 and 6... I can't seem to get the join correct once I'm looking for multiple activities. Any help would be appreciated.

> SELECT distinct(t1.name)
> FROM parks as t1, activities as t2, contains as t3
> WHERE t1.id=t3.park_id and t2.id=t3.activity_id
> AND t3.activity_id IN(1,4,6)

Oops! Sorry, you might want to change the 't3.activity_id IN(1,4,6)' to 't3.activity_id=1 AND t3.activity_id=4...' etc to check for multiple activities

I've tried what you've suggested but it still doesn't find multiple activities. The structure of the contains table is given in the original post. So if park 2 contains activities 1,3,5 there are 3 records in the table 2,1 2,3 2,5 I can find that park 2 has activity 1 with both my original query and the one suggested, but neither is able to find that park 2 has activity 1 and activity 3. is this a problem with my tables or am I just getting the query wrong?

An alternate suggestion would be to rework the parks table to include a set() datatype....

> create table parks(
> id int not null auto_increment primary key,
> name varchar(25),
> activity_list set('1','2','3','4'...'n'));


Then you could use a query like
> select name from parks
> where find_in_set(1, activity_list)
> AND find_in_set(3, activity_list)
> AND find_in_set(6, activity_list)..etc;

You could probably just substitute the numbers for the actual activity name, i.e. 'tennis', 'softball'....whatever. I believe the set() datatype can hold up to 64 members - check the mysql manual

ok this is sick and hideous to do dynamically, but I figured I'd share the solution I came up with. Basically for X number of activities being searched for I do X self joins on the contains table. So to find all parks with activites 1,3,5 X=3 and the query is
SELECT distinct(t3.park_id) FROM contains AS t1, contains AS t2, contains AS t3 WHERE t1.activity_id =1 and t2.activity_id =3 and t3.activity_id =5;

Have to thank Kyuzo for all your help

if anyone has a better way to do this I'd love to hear it

jamie

ok this is sick and hideous to do dynamically, but I figured I'd share the solution I came up with. Basically for X number of activities being searched for I do X self joins on the contains table. So to find all parks with activites 1,3,5 X=3 and the query is
SELECT distinct(t3.park_id) FROM contains AS t1, contains AS t2, contains AS t3 WHERE t1.activity_id =1 and t2.activity_id =3 and t3.activity_id =5;

Have to thank Kyuzo for all your help

if anyone has a better way to do this I'd love to hear it

jamie