mysql - One Row to Match Them All -

so have situation have 2 tables. 1 base table (called _keys in example), unique primary key. there table multiple rows of data each id in _keys (this second table extra).

i need select largest value each primary key in _keys extra. have made sqlfiddle model problem here.

this query i'm using, issue select 1 value table, not 1 value per row.

select * _keys left join (select * order value2 desc limit 1) e on e.id = _keys.id; 

for example sql fiddle used database schema:

create table _keys(id int, value int); insert _keys (id, value) values (1, 5),(2, 3),(3, 4);  create table extra(id int, value2 int); insert (id, value2) values (1, 3),(1, 1),(2, 4),(2, 6),(3, 3),(3, 5); 

basically result here. first row _keys table gets data second table.

in mysql, how can achieve selecting 1 row extras each row in _keys?

i believe understand trying i'm not sure.

• you getting null values because of limit, returns first row. need use group by.
• to largest value, can use max.

try this.

select * _keys left join (select id, max(value2) value2 group id) e on e.id = _keys.id;