MYSQL：选择与另一个表的多行相关的条目

Question

I have two tables in a database:

• entries (ID, distance, status[,...])
• entriesmeta (ID, entry_ID, metakey, metavalue)

Until now, I select entries like this:

SELECT ID,
distance
FROM entries
WHERE status = '2' HAVING distance < 30
ORDER BY distance
LIMIT 20

Now, the second table contains data of this sort:

ID | entry_ID | metakey | metavalue
1  | 137      | service | 13
2  | 137      | service | 7
3  | 137      | service | 76
4  | 84       | service | 23
etc.

The entries in the first table are restaurants, the services in the second table are services they offer. What I have been trying for a while now is this: Create a HTML search form that lets users choose certain services via checkboxes. The MYSQL query then should select all entries that offer ALL those services. So far, the search form puts the selected services in an array (7,13,9,27).

My problem is finding the correct database query. It should match the metavalue-field with the checkbox-array and then select all entries from the first table, which offer all those services. I have been experimenting with "LEFT JOIN ON entries.ID = entriesmeta.entry_ID" and several WHERE clauses, but no success. I wonder if someone can help.

Thank you in advance - and sorry for my bad English.

Answer 1

SELECT  e.ID
FROM    entries e
        INNER JOIN entriesmeta m
            ON e.ID = m.entry_id
WHERE   
        m.metakey = 'service' AND
        m.metavalue IN (7,13,9,27)
GROUP   BY e.ID
HAVING  COUNT(DISTINCT m.metavalue) = 4

• SQLFiddle Demo

This query will display all ID that has metakey of service and a value(s) of 7, 13, 9, 27.