It's currently late, so excuse me if I'm wrong. Is this, what you like to have?
select sl.source_id, max(score) from source s inner join sourcelevel sl on s.source_id = sl.source_id inner join level l on sl.level_id = l.level_id group by 1 This is the highest score per source_id. 2011/1/12 George Francis <gfranc...@gmail.com> > hmm, but if I try to constrain the inner query to the source_id of the > outer query I get an error as follows: > > select source.source_id, score, name from source > left join ( select * from sourcelevel, level where sourcelevel.level_id = > level.level_id and sourcelevel.source_id = source.source_id order by score > desc limit 1 ) > as temp on temp.source_id = source.source_id; > > ERROR: invalid reference to FROM-clause entry for table "source" > > LINE 14: ...l_id = level.level_id and sourcelevel.source_id = source.sou... > > ^ > > HINT: There is an entry for table "source", but it cannot be referenced > from this part of the query. > > > > ********** Error ********** > > > ERROR: invalid reference to FROM-clause entry for table "source" > > SQL state: 42P01 > > Hint: There is an entry for table "source", but it cannot be referenced > from this part of the query. > > Character: 601 > > > On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic < > jasmin.dizdare...@gmail.com> wrote: > >> Because of the "score desc limit 1". The subselect returns only the higest >> score and this is level 2. >> >> 2011/1/12 George Francis <gfranc...@gmail.com> >> >> 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 >>> >> >> > > > -- > George Francis > e-mail: gfranc...@gmail.com >
