Query Help Joining Tables

[nexit]

I have a question about how I would structure a query.

 

I have 2 tables.

 

Store

store_id -- primary key

zip_code

 

Merchandise

item_id -- primary key

item_name

description

cost

storeID -- foreign key that references the store_id from the store.

 

I am using InnoDB. 

 

I want the user to be able to input 1-5 choices that are 1 word apiece and it would cross reference the decription and tell me the store that has them both.  For instance, choice 1 = clock, choice 2 = paper, and choice 3 = food.  It would query a result that would give me the stores that have items that match the description of them all.  For instance, it would return Wal - Mart based on this query because it would be the only store that had an item to match each description.  Any help is much appreciated.  Thanks!

 

 

 

[Barand]

try

SELECT DISTINCT
    s.store_id
  , s.zip_code
FROM
    store s
    INNER JOIN merchandise m1 ON s.store_id = m1.storeID AND m1.description = 'clock'
    INNER JOIN merchandise m2 ON s.store_id = m2.storeID AND m2.description = 'paper'
    INNER JOIN merchandise m3 ON s.store_id = m3.storeID AND m3.description = 'food'