Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL join on max value

Tags:

join

mysql

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
like image 323
bsod99 Avatar asked Mar 27 '13 18:03

bsod99


People also ask

How do you use Max in inner join?

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.

Is full join possible in MySQL?

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.

Can we use Max in Where clause in MySQL?

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.

How do I find the maximum value of two tables in SQL?

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 ...


2 Answers

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.

like image 89
Joachim Isaksson Avatar answered Nov 10 '22 11:11

Joachim Isaksson


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

like image 22
Taryn Avatar answered Nov 10 '22 10:11

Taryn