Jump to content

Looking for the most efficient way to do this


gabeg

Recommended Posts

I have two tables, one is called menuitem, and has columns id, category, name, description. Then I have a table called menuprice, with columns menu_id, size, price.

 

Say I have menu item called pizza with id 2, I want to select all the rows from menuprice that match menu_id = 2.

 

I can make the call to show all the in from the menu item table, but is there is there someway I can display the price for each different size /price without making tons of sub_queries? When there begins to be a lot of menu items, querying the database for each item adds up

Link to comment
Share on other sites

Are you looking for this:

 

SELECT * FROM menuitem JOIN menuprice ON (menuitem.id = menuprice.menu_id)

 

That will give you duplicate rows for an item when there are different sizes, so you can no longer assume each item is listed once, which you probably could when you queried from menuitem only.

Link to comment
Share on other sites

Are you looking for this:

 

SELECT * FROM menuitem JOIN menuprice ON (menuitem.id = menuprice.menu_id)

 

That will give you duplicate rows for an item when there are different sizes, so you can no longer assume each item is listed once, which you probably could when you queried from menuitem only.

Yeah I know I can do that, but the duplicate rows kills what I'm trying to do

Link to comment
Share on other sites

Use DISTINCT or GROUP BY to remove the duplicates.

Definitely group by, DISTINCT is the most useless modifier I've ever come across (except inside a COUNT() ).

 

or GROUP_CONCAT

Useless? Or did you mean something else?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.