CODEWITHSUNDEEP
phpmysqlsql
svenniferlopez
svenniferlopez

Reputation: 11

MYSQL: Select entries in relation to multiple rows of another table

I have two tables in a database:

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.

Upvotes: 1

Views: 2786

Answers (2)

John Woo
John Woo

Reputation: 263893

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

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

Upvotes: 5

Matthew
Matthew

Reputation: 9949

This would get you all entries which have a given entriesmeta service value - which is what I think you are asking.

 SELECT entries.* from entriesmeta
 INNER JOIN entries on entries.ID = entriesmeta.entry_ID
 WHERE entriesmeta.metavalue = <myValue>

Upvotes: 2

Related Questions