Reputation: 1297
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
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
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)
Upvotes: 11
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
Upvotes: 4