SQL Server: Subquery on a join -

i have 2 tables schema , data below. table has id , associated name. table b associates id table a price , otherattr. each entry in table a, there may multiple entries different prices in table b, otherattr same each entry.

given id table a, select name, otherattr, , minimum price.

the below query returns multiple results, need write query return single result minimum price.

select a.name, b.price, b.otherattr  a left join b b on b.idfroma = 1 a.id = 1       table        table b      id | name      idfroma | price | otherattr     --------       ---------------------------     1 |          1       | 200   | abc     2 | b          1       | 300   | abc                    1       | 400   | abc                    2       | 20    | def                    2       | 30    | def                    2       | 40    | ef 

i massively oversimplified example. in addition selecting min price , otherattr table b, have select bunch of other attributes joins on other tables. why thinking group , min should subquery of join on table b, way avoid grouping attributes selecting (because attributes being selected vary programmatically).

the actual query looks more like:

select a.name, b.price, b.otherattr, c.x, c.y, d.e, d.f, g.h.... a left join b b on b.idfroma = 1 left join c c on something... left join d d on something... left join g g on something...  a.id = 1 

to this, use group in inner query:

select gd.name, gd.price,gd.otherattr, c.x, c.y, d.e, d.f, g.h....  (select a.id,a.name, min(b.price) price, b.otherattr  a left join b b on b.idfroma = 1 a.id = 1 group a.id,a.name,b.otherattr) gd left join b b on b.idfroma = 1 left join c c on something... left join d d on something... left join g g on something...