[SQL] joining one record according to max value

[George Francis]

The following sql is intended to produce  a list of all unique SOURCEs,
along with the corresponding LEVEL.NAME for LEVEL with highest SCORE value
via SOURCELEVEL.

I'd like to know why it doesn't return a SCORE value for SOURCE with id 3,
despite there being a SOURCELEVEL entry for it.

Many thanks,


drop table if exists source;

drop table if exists sourcelevel;

drop table if exists level;


create table source ( source_id int );

create table sourcelevel ( source_id int, level_id int);

create table level ( level_id int, score int, name text );


insert into source values (1), (2), (3);

insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
'kappa' );

insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );


select source.source_id, score, name from source

left join ( select * from sourcelevel, level where sourcelevel.level_id =
level.level_id order by score desc limit 1 )

as temp on temp.source_id = source.source_id;

-- 
George