多表和mm表的mysql语句

Question

i need some help for a mysl-statement. i have a table for "hotels" and a table for "pictogram". both are relatate with mm relation in table hotels_pictogram_mm. on the website the users can choose several pictogram in a search box. so i want to get all hotels which have for example "wellness" AND "free wifi". how must the sql statement be for this search.

following of course does not work:

SELECT *
FROM hotel 
LEFT JOIN hotel_pictogram_mm ON hotel.uid=hotel_pictogram_mm.uid_local 
LEFT JOIN pictogram ON hotel_pictogram_mm.uid_foreign=pictogram.uid 
WHERE 
pictogram.id = 3
AND pictogram.id = 5

thanks for help! martin

Answer 1

You need to join against pictogram twice, once for each id:-

SELECT *
FROM hotel 
INNER JOIN hotel_pictogram_mm ON hotel.uid=hotel_pictogram_mm.uid_local 
INNER JOIN pictogram p3 ON hotel_pictogram_mm.uid_foreign=p3.uid 
INNER JOIN pictogram p5 ON hotel_pictogram_mm.uid_foreign=p5.uid 
WHERE p3.id = 3
AND p5.id = 5

You could join once and use count to check the number of matching ids from pictogram, but depends whether you want columns other than those in the hotel table.