 UBBFriend: Email This Page to Someone! |
 DevShed Discussion Forums
 MySQL
 help with this join
|
| next newest topic | next oldest topic |
| Author | Topic: help with this join |
|
strider3700 Junior Member |
posted June 27, 2000 11:06 AM
  
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. |
|
Kyuzo Member |
posted June 28, 2000 06:28 AM
> 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) |
|
Kyuzo Member |
posted June 28, 2000 06:33 AM
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 |
|
strider3700 Junior Member |
posted June 28, 2000 11:54 AM
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? |
|
Kyuzo Member |
posted June 28, 2000 03:12 PM
An alternate suggestion would be to rework the parks table to include a set() datatype.... > create table parks(
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 |
|
strider3700 Junior Member |
posted June 28, 2000 03:55 PM
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 |
|
strider3700 Junior Member |
posted June 28, 2000 04:48 PM
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 |
All times are MST (US) | next newest topic | next oldest topic |
  |
|
Contact Us | DevShed.com
Copyright © 1997-2000 ngenuity. All rights reserved.
Powered by: Ultimate Bulletin Board, Version 5.41a
© Infopop Corporation (formerly Madrona Park, Inc.), 1998 - 1999.