多表和mm表的mysql语句

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

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.

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

This query will have empty set since pictogram.id = 3 AND pictogram.id = 5. CANT BE TRUE AT SAME TIME.Use OR instead.

So you should use

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
    OR pictogram.id = 5

Note : Since you are doing LEFT JOIN on pictogram , and using pictogram.id = 3 in WHERE it will act like INNER JOIN.

Insted you can do it like this

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
 AND (pictogram.id = 3 OR pictogram.id = 5)

Hope this helps.

Assuming there is id for hotel, you can do self join to the result set , it self. This will work

SELECT T1.*
FROM
(SELECT *,hotel.`id` 
FROM hotel 
INNER JOIN hotel_pictogram_mm ON hotel.uid=hotel_pictogram_mm.uid_local 
INNER JOIN pictogram ON hotel_pictogram_mm.uid_foreign=pictogram.uid 
WHERE 
pictogram.id = 3) T1 INNER JOIN 
(SELECT hotel.`id`
FROM hotel 
INNER JOIN hotel_pictogram_mm ON hotel.uid=hotel_pictogram_mm.uid_local 
INNER JOIN pictogram ON hotel_pictogram_mm.uid_foreign=pictogram.uid 
WHERE 
pictogram.id = 5) T2 ON T1.`id`=T2.`id`

Hope this helps.