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
If you want to use a different date column, just change that: SELECT fp. * FROM facebook_posts fp JOIN( SELECT id, MAX(updated_at) AS latestUpdate FROM facebook_posts GROUP BY id) t ON t.id = fp.id AND t.
MySQL doesn't offer syntax for a full outer join, but you can implement one using the union of a left and a right join.
MySQL MAX() Function with WHERE ClauseThe WHERE clause allows us to filter the result from the selected records. The following statement finds the maximum income in all rows from the employee table. The WHERE clause specifies all those rows whose emp_age column is greater than 35.
select id from T1 where price in( select max(price) from( select max(price) as price from T1 union select max(price) as price from T2 union select max(price) as price from T3 ) temp ) union select id from T2 where price in( select max(price) from( select max(price) as price from T1 union select max(price) as price from ...
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With