sql server - SQL - generate a list of cheapest options -

the schema little bit strange. have 2 tables:

create table [dbo].[events](     [id] [int] not null,     [name] [varchar](50) not null,     [venueid] [int] null )   create table [dbo].[venues](     [id] [int] not null,     [name] [nvarchar](50) not null,     [averageprice] [int] not null )  

and data this:

insert venues (id, name, averageprice) values   (1, 'arena1', 100),   (1, 'arena2', 200),   (1, 'arena3', 50),   (2, 'club1', 50),   (2, 'club2', 150)   insert events (id, name, venueid) values    (1, 'consertatarena1', 1),    (2, 'consertatarena2', 1),    (3, 'consertatarena3', 1),    (2, 'conference', null) 

i need produce: 'consertatarena3', 'conference'. cheapest venue option events same venueid plus events null venueid. tables quite massive, few million rows. efficient select it?

the database sql server 2012 standard.

it looks have problem in way store data. in example 'concertatarena1', 'concertatarena2' , 'concertatarena3' linked same venue defined multiple times in venues table.

why isn't correct answer 'concertatarena1' event 'arena3' since id of venue same. using kind of fussy logic on name there?

i suspect should either store arenas different venues or add sub-table have price , each arena unique. here's example added table called arenas:

declare @events table (     [id] [int] not null,     [name] [varchar](50) not null,     [arenaid] [int] null )   declare @venues table(     [id] [int] not null,     [name] [nvarchar](50) not null )   declare @arenas table(     [id] [int] not null,     [venueid] int not null,     [name] [nvarchar](50) not null,     [averageprice] [int] not null )    insert @venues (id, name) values  (1, 'venue1'), (2, 'venue2')  insert @arenas (id, venueid, name, averageprice) values   (1,1, 'arena1', 100),   (2,1, 'arena2', 200),   (3,1, 'arena3', 50),   (4,2, 'club1', 50),   (5,2, 'club2', 150)   insert @events (id, name, arenaid) values    (1, 'consertatarena1', 1),    (2, 'consertatarena2', 2),    (3, 'consertatarena3', 3),    (4, 'conference', null)  ;with ranked ( select e.id, a.averageprice, row_number() on (partition a.venueid order a.averageprice) rn  @events e left outer join @arenas on a.id = e.arenaid)  select e.id,e.name,r.averageprice @events e inner join ranked r on r.id = e.id r.rn = 1 

with query you'l 'concertatarena3' average price 50 , 'conference' average price null