mysql - How come this SQL query works? -

this exercise wikibooks, #6

the table schema

the question is:

for each piece, find expensive offering of piece , include piece name, provider name, , price (note there 2 providers supply same piece @ expensive price).

the solution:

select pieces.name, providers.name, price    pieces inner join provides on pieces.code = piece                inner join providers on providers.code = provider    price =    (      select max(price) provides      piece = pieces.code    ); 

i don't understand subquery part. think typical way find max price

 select max(price) provides  group piece; 

in way, can not use price = subquery or price in subquery, , sub query in solutions, looks like

select max(price) provides, pieces provides.piece=pieces.code; 

it return biggest number, can not figure out why can 'group' , return right rows.

although thomas provide simpler solution, lets original question, why work.

select pieces.name, providers.name, price    pieces inner join provides on pieces.code = piece                inner join providers on providers.code = provider    price =    (      select max(price) provides      piece = pieces.code    ); 

first, hate working correlated subqueries in answer. correlated subquery 1 subquery processed once each record. notice outer part of query provides "pieces" table reference. inner query saying "provides" table, give me maximum price current "pieces.code" value. after that, simple join other tables grab piece , provider details.

my personal preference pre-aggregate subquery once on "provides" table codes own group by. runs query once, grouped there 1 record per respective code. more see queries prevent larger overhead. also, work alias names, if dealing ex using alias.

from longtablenamesinyourdatabase ltn 

and whenever work multiple tables, try provide table.column or alias.column others trying in future know specific column coming , not guessing.

select        p.name,        prov.name,        maxbypiece.maxprice          ( select                pr1.piece,               max( pr1.price ) maxprice                           provides pr1            group               pr1.piece ) maxbypiece          join provides pr2 on maxbypiece.piece = pr2.piece , maxbypiece.maxprice = pr2.price            join pieces p on pr2.piece = p.code            join providers prov on pr2.provider = prov.code 

it may more complex, more applicable if have multiple tables multiple rows given thing (contract, order, person, sales rep, whatever) , otherwise end cartesian result , wonder why duplicates in counts or totals.

the first clause query nothing maximum price per piece , use alias pr1 differentiate next join. join after provides can find pieces @ price. remember question wanted providers @ maximum price. now, have records qualified @ max price per piece , provider. finish joining lookup tables can names