#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    8
    Rep Power
    0
    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    > 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)
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    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
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    8
    Rep Power
    0
    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?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    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
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    8
    Rep Power
    0
    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
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    8
    Rep Power
    0
    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

Similar Threads

  1. Why is outer join so much slower than inner join?
    By VitalyB in forum MySQL Help
    Replies: 2
    Last Post: October 11th, 2003, 06:44 PM
  2. Problems with INNER JOIN
    By Kevin Andreshak in forum MySQL Help
    Replies: 1
    Last Post: July 19th, 2003, 01:07 AM
  3. Replies: 1
    Last Post: April 14th, 2003, 10:36 AM
  4. 2 tables LEFT JOIN(?) to 1 other table
    By miked2miked in forum MySQL Help
    Replies: 0
    Last Post: March 31st, 2003, 01:37 PM
  5. SELECT where data is comma delimited..
    By Robert_J_Sherman in forum MySQL Help
    Replies: 29
    Last Post: June 28th, 2001, 10:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo