I'm programming a website in which we have sport clubs listed in a DB, structure is as follows:
club (id_club, club_name, club_city)
court (id_court, court_name, club_id_club (FK), court_type)
court_has_sport (court_id_court, sport_id_sport)
sport (id_sport, sport_name)
So as you can imagine, a court may allow more than one sport to be played in it (for instance, soccer and basketball).
The thing is, I'm setting up a search feature through which the user has to be able to search for a court both in their city and allowing a sport of their choice.
This can be done one at a time, or both at a time: you can first browse for all courts in your city, or all courts in all cities involving a certain sport. Now, I have both search queries independently, but my problem is when I have to perform a search using both criteria.
I've tried several methonds, such as JOINs (I don't know how to use them very well tho). The queries I use are:
Selecting a city:
Expand|Select|Wrap|Line Numbers
- SELECT c.id_court, c.club_id_club, c.court_name, c.court_type FROM court c,club WHERE club.id_club = c.club_id_club AND club.city='Somecity' GROUP BY id_court
Expand|Select|Wrap|Line Numbers
- SELECT c.id_court, c.club_id_club, c.court_name, c.court_type FROM court_has_sport cd, sport d, cancha c WHERE cd.sport_id_sport = (SELECT id_sport FROM sport WHERE sport_name= 'Tennis' GROUP BY id_sport) AND cd.court_id_court= c.id_court GROUP BY c.id_court
I think (I'm pretty sure actually) both queries can be rewritten in a better way, but SQL is not my speciality.
I hope you guys can help me.
Thanks