I have two tables in a restaurant search done in php. All the information about the restaurant type, facilities, cuisines are in entered to Table2 -'stack' with reference to their restuarant id in the table 1. How do i run a query so that i can get all the restaurant that serve chineese and also serves dinner and also have parking?
This doesn't seems to work:
SELECT DISTINCT restaurant.name, restaurant.place
FROM stack,restaurant
WHERE restaurant.id=stack.rest_id AND stack.value='chineese'
AND stack.value='dinner' AND stack.value='parking'
Here is my table structure
Table1 - **restaurant**
------+----------+----------
id + name + place
------+----------+----------
1 rest1 ny
2 rest2 la
3 rest3 ph
4 rest4 mlp
Table2 - **stack**
------+----------+-------------------------
id + rest_id + type + value
------+----------+-------------------------
1 1 cuisine chinese
2 1 serves breakfast
3 1 facilities party hall
4 1 serves lunch
5 1 serves dinner
6 1 cuisine seafood
7 2 cuisine Italian
8 2 serves breakfast
9 2 facilities parking
10 2 serves lunch
11 2 serves dinner
12 2 cuisine indian
Also tell me if this is the wrong method. I used stack, because cuisine, facilities all can be unlimited as its not defined and very for each.
Given your existing structure, that's quite easy:
Just make sure that the numeric value given to
HAVING COUNT(rest_id)
matches the number of values that you are searching for. Here's a simple test case (note that I have added another restaurant, which actually has 'chinese', 'dinner' and 'parking':Alternatively, you could create related tables, like this (but this is probably not the best structure):
The query is almost the same, you just need your subquery to produce the appropriate join:
Here's some sample SQL for the above structure: