How to get intersecting results from multiple queries (Joins)?

[Ishegg]

Hello.

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:

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

Selecting a sport:

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

Here's the important part: now I need to have a MySQL query which returns tennis courts located in Somecity (according the example).

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

[dgreenhouse]

I'm not sure what platform you're using for your web application is (i.e. PHP, .NET, Java, etc.), but you can dynamically create the "WHERE" clause to determine the criteria required.

i.e.
1- City
2- City and a sport
3- City and multiple sports

Obviously if there are multiple sports, you'll need to use an 'OR' or an 'IN' in the where clause.

i.e.
1- WHERE sport = 'x' OR sport = 'y'
2- WHERE sport in ('x'); WHERE sport in ('x','y')