In article <[EMAIL PROTECTED]>, Salzgeber Olivier <[EMAIL PROTECTED]> writes:
> I need to create a search Query to find all the hotels which have specific > services and sparetime activities. > For example: > Search for hotels where location is "Bern" and the hotel has sparetime > activity 2 and 5 AND services 5 and 3 and 7 > So only hotels which match all the expressions should appear as results. > I've tried it with the following Query but this doesn't work as it should. > **************************************************************************** > * > SELECT * > FROM stammdaten > INNER JOIN relation_services ON stammdaten.id_PK = relation_services.hotelid > INNER JOIN relation_sparetime ON stammdaten.id_PK = > relation_sparetime.hotelid > WHERE > stammdaten.location LIKE 'Bern' AND > relation_services.serviceid IN (5, 3, 7 ) AND > relation_sparetime.sparetimeid IN (2, 5) AND > GROUP BY stammdaten.name > ORDER BY stammdaten.name This gives all hotels having at least one of the required services/sparetimes, i.e. an OR condition. For AND you need SELECT stammdaten.id_PK FROM stammdaten INNER JOIN relation_services ON stammdaten.id_PK = relation_services.hotelid INNER JOIN relation_sparetime ON stammdaten.id_PK = relation_sparetime.hotelid WHERE stammdaten.location = 'Bern' AND relation_services.serviceid IN (5, 3, 7) AND relation_sparetime.sparetimeid IN (2, 5) GROUP BY stammdaten.id_PK HAVING count(DISTINCT relation_services.serviceid) >= 3 AND count(DISTINCT relation_sparetime.sparetimeid) >= 2 ORDER BY stammdaten.id_PK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
