Looking for the most efficient way to do this

[gabeg]

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

[btherl]

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.

[gabeg]

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

[bubblegum.anarchy]

Use DISTINCT or GROUP BY to remove the duplicates.

[fenway]

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() ).

[bubblegum.anarchy]

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

[fenway]

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?

[bubblegum.anarchy]

no no... DISTINCT is also useful in a GROUP_CONCAT

[fenway]

no no... DISTINCT is also useful in a GROUP_CONCAT

Ah, right... of course, my bad.