Jump to content
nexit

Query Help Joining Tables

Recommended Posts

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!

 

 

 

Share this post


Link to post
Share on other sites

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'

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.