SQL Complicated Group / Join by Category
- by Mike Silvis
I currently have a database structure with two important tables.
1) Food Types (Fruit, Vegetables, Meat)
2) Specific Foods (Apple, Oranges, Carrots, Lettuce, Steak, Pork)
I am currently trying to build a SQL statement such that I can have the following.
Fruit < Apple, Orange
Vegetables < Carrots, Lettuce
Meat < Steak, Port
I have tried using a statement like the following
Select * From Food_Type join (Select * From Foods) as Foods on Food_Type.Type_ID = Foods.Type_ID
but this returns every Specific Food, while I only want the first 2 per category. So I basically need my subquery to have a limit statement in it so that it finds only the first 2 per category.
However if I simply do the following
Select * From Food_Type join (Select * From Foods LIMIT 2) as Foods on Food_Type.Type_ID = Foods.Type_ID
My statement only returns 2 results total.
