SQL Complicated Group / Join by Category

Question

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.

check out http://stackoverflow.com/questions/2596489/how-to-select-the-last-two-records-for-each-topic-id-in-mysql — Salil, May 12 '10 at 08:14
the query to solve this may rely on syntax that is database specific or depends on what is supported in your database version. Not many people will want to write your query in SQL Server to just find out you are using MySql, that is why it is important to list the database you are using. — KM., May 12 '10 at 12:41

score 1 · Accepted Answer · edited May 23 '17 at 10:27

Answered here, but that answer was mysql specific in the end.

If your database is a more compliant to the standard you can also use PARTITION

Note:

Select * 
From Food_Type join 
     (Select * From Foods LIMIT 2) as Foods on Food_Type.Type_ID = Foods.Type_ID

(Select * From Foods LIMIT 2) returns only two records out of the whole table, the join happens only after the query is returned.

To do what you are thinking about you will have to use nested subqueries.

Select * 
From Food_Type join 
     Foods on Food_Type.Type_ID = Foods.Type_ID
Where Foods.Foods_ID IN (Select * 
                         From Foods AS nested 
                         Where nested.Type_ID = Food_Type.Type_ID 
                         ORDER BY ? 
                         LIMIT 2)

PARTITION/RANK approach is different and should be faster, if you need help let us know.

SQL Complicated Group / Join by Category

1 Answers1