CODEWITHSUNDEEP
mysqljoin
bsod99
bsod99

Reputation: 1297

MySQL join on max value

From joining the tables below on the entry.id, I want to extract the rows from the food_brands table which have the highest type_id - so I should be getting the top 3 rows below, with type_id 11940

food_brands

        id      brand       type_id 
        15375   cesar       11940
        15374   brunos      11940
        15373   butchers    11940
        15372   bakers      11939
        15371   asda        11939
        15370   aldi        11939

types

        id      type      quantity      food_id 
        11940   comm      53453         10497
        11939   comm      999           10496

foods

        id      frequency   entry_id 
        10497   twice       12230
        10496   twice       12230
        10495   once        12230

entries

         id     number  
         12230  26

My attempt at the query isn't filtering out the lower type.id records - so from the table records below in food_brands, i'm getting those with type_id 11940 and 11939. Grateful for any help fix this!

SELECT fb.*
                        FROM food_brands fb
                        INNER JOIN types t ON fb.type_id = t.id
                        INNER JOIN
                        (
                            SELECT MAX(id) AS MaxID
                            FROM types
                            GROUP BY id
                        ) t2 ON t.food_id = t2.food_id AND t.id = t2.MaxID
                        INNER JOIN foods f ON t.food_id = f.id
                        INNER JOIN entries e ON f.entry_id = e.id
                        WHERE entries.id = 12230

Upvotes: 13

Views: 34057

Answers (3)

didierc
didierc

Reputation: 14750

I don't know why you are doing all these inner joins after the one on the t2 subquery, since you are only retrieving the columns of fb, but I suppose that you are not showing the whole query, and you just want to get that one fixed.

The issue is actually in the subquery t2: there, for some untold reason, you choose to do a GROUP BY id which changes the MAX function semantic to generate a maximum value per id, and since you are asking the maximum on that very column, MAX and GROUP BY cancel out each other. Just removing the GROUP BY clause fixes the query.

If for some untold reason you cannot remove that clause, perhaps replacing MAX(id) by id and adding ORDER BY id DESC LIMIT 1 would do.

Also, your subquery should probably select also food_id since it is used in the subsequent INNER JOIN clause.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

A simple subquery should do it just fine;

SELECT * FROM food_brands WHERE type_id=
  (SELECT MAX(t.id) tid FROM types t
   JOIN foods f ON f.id=t.food_id AND f.entry_id=12230)

An SQLfiddle to test with.

Upvotes: 11

Taryn
Taryn

Reputation: 247880

If you just want to return the rows from food_brands with the max type id, you should be able to use:

SELECT fb.*
FROM food_brands fb
INNER JOIN
(
  select max(id) id
  from types
) t
  on fb.type_id = t.id

See SQL Fiddle with Demo

Upvotes: 4

Related Questions