mysql - SQL Querying: How to Find the Maxima of Certain Columns within Different Sets of Similar Records? -

how find secret ingredients used in pizzas have won prize? i’m missing sql condition in comment below:

 select r.name, p.secret_ingredient restaurants r  join restaurant_has_pizzas rhp on rhp.restaurant_id = r.id  join pizzas p on p.id = rhp.pizza_id  join awarded_prizes on a.id = r.latest_prize_id  r.owner = 'me!'    , p.created_at < a.won_at -- , p young/new possible, i.e., p.created_at close --   a.won_at possible; i’m interested in winning pizzas --   have been made right before taster awarded prize!  ; 

the query far returns kinds of ingredients used in restaurants have won prize. however, i’m interested in those secret ingredients used in winning pizzas. note chefs have created newer pizzas since have won last prizes.

here’s ddl:

create table pizzas     (`id` int not null auto_increment,      `created_at` datetime not null,      `secret_ingredient` varchar(42) not null,      primary key (`id`)) ; insert pizzas     (`created_at`, `secret_ingredient`) values     ('2012-11-01', 'peas'),     ('2012-12-01', 'pepper'),     ('2012-12-11', 'pork'),     ('2012-12-21', 'peanuts'),     ('2012-12-31', 'oranges'),     ('2013-01-02', 'ham'),     ('2013-01-20', 'oranges'),     ('2013-01-21', 'root beer'),     ('2013-03-22', 'mushrooms') ;  create table awarded_prizes     (`id` int not null auto_increment,      `won_at` datetime not null,      primary key (`id`)) ; insert awarded_prizes     (`won_at`) values     ('2012-12-23'),     ('2013-02-02') ;  create table restaurants     (`id` int not null auto_increment,      `name` varchar(42) not null,      `owner` varchar(42) not null,      `latest_prize_id` int,      primary key (`id`),      constraint `fk_restaurants_awarded_prizes1`        foreign key (`latest_prize_id`)        references `awarded_prizes` (`id`)) ; insert restaurants     (`name`, `owner`, `latest_prize_id`) values     ('don camillo', 'me!', 1),     ('tasty pizzas', 'me!', 2),     ('b. r.', 'don alphonso', null) ;  create table restaurant_has_pizzas     (`restaurant_id` int not null,      `pizza_id` int not null,      primary key (`restaurant_id`, `pizza_id`),      constraint `fk_restaurant_has_pizzas_restaurants1`        foreign key (`restaurant_id`)        references `restaurants` (`id`),      constraint `fk_restaurant_has_pizzas_pizzas1`        foreign key (`pizza_id`)        references `pizzas` (`id`)) ; insert restaurant_has_pizzas     (`restaurant_id`, `pizza_id`) values     (1, 1),     (1, 2),     (1, 3),     (1, 4),     (1, 5),     (2, 6),     (1, 7),     (2, 8),     (2, 9) ; 

see this sql fiddle. award-winning ingredients “peanuts” “don camillo” , “root beer” “tasty pizzas”. don’t want see other ingredients returned sql query. expected result table:

name         | secret_ingredient -------------|------------------ don camillo  | peanuts tasty pizzas | root beer 

background

ok, admittedly crafted example based on more complex real life db schema … latter boring compared restaurant empire show here :-)

 select a.name       , b.secret_ingredient            ( select r.*              , max(p.created_at) max_created_at            restaurants r            join awarded_prizes z              on z.id = r.latest_prize_id            join pizzas p              on p.created_at <= z.won_at           group              r.id       )     join pizzas b       on b.created_at = a.max_created_at;  +--------------+-------------------+  | name         | secret_ingredient |  +--------------+-------------------+  | don camillo  | peanuts           |  | tasty pizzas | root beer         |  +--------------+-------------------+ 

i'm not convinced either of these ingredients belong anywhere near pizza.