How to construct the query

Question

I am confused with building the query. I am implementing the search feature in a restaurant site. User first selects the area he is searching for restaurant and then he select the food types like chinese, japanese, thai from the checkboxes.

After selecting all these, the restaurants which providing the selected food in the selected area will be displayed. I am succeeded in getting the pincodes and food types from the yser.

I hav two table with the following fields,

restaurant_dp table with fields

id  -ID of the restaurant
pcode -pincode of the area

restaurant_iw table with fields

id  - id of the restaurant
menu  - menu the restaurant provides (eg., Chinese, thai etc.,)

My confusion is how to fetch the records from both the tables with the conditions:

• pcode = Userselectedpincode
• menu = userselected menu

Plz help. Any help will be appreciated

Answer 1

select dp.id
from restaurant_dp dp
inner join restaurant_iw iw on dp.id = iw.id
where dp.pcode = Userselectedpincode 
    and iw.menu = userselectedmenu

Answer 2

select * from restaurant_dp dp join restaurant_iw iw on dp.pcode = iw.pcode where dp.pcode = $userselectedpincode and iw.menu = $userselectedmenu;

then you just have to make sure that the two variables used in the query are populated properly.

Answer 3

SELECT * 
FROM restaurant_dp AS dp
LEFT JOIN restaurant_iw AS iw
ON dp.IDrestaurant= iw.IDrestaurant
WHERE dp.pcode = Userselectedpincode 
AND iw.menu = userselectedmenu

Answer 4

SELECT DISTINCT dp.id FROM restaurant_dp dp INNER JOIN restaurant_iw iw ON dp.id = iw.id WHERE dp.pcode = $pcode AND iw.menu = $menu

Answer 5

You really should merge these to one table, with all fields relating to a certain restaurant in the same row. If you do that, the query simply becomes SELECT * FROM restaurants WHERE pcode = 'code' AND menu = 'menu';. Your current setup makes things more difficult, requiring a join:

SELECT * FROM restaurant_iw AS iw
LEFT JOIN restaurant_dp AS dp ON iw.id = dp.id
WHERE dp.pcode = 'code' AND iw.menu = 'menu';