sql - primary table joined to foreign table - select only one record from foreign table -

sql - primary table joined to foreign table - select only one record from foreign table -

using sql server 2012. have table called deals contains primary key called deal_id along 10 other fields. have table called deals_country contain foreign key called deal_id.

it's possible record in deals contains numerous records in deals country. know query below homecoming records 2 tables.

select deals .*, deals_country.* deals inner bring together deals_country on deals .deal_id = deals_country.deal_id order deals .deal_id

what select records deals , select 1 corresponding record deals_country table, doesn't matter record takes deals_country. below,

deals deal_id other fields mn13 mn14 mn15 mn28 deals_country deal_id country mn13 nl mn13 fr mn14 gb mn14 mn15 mn28 ca mn28 mn28 mx

the result see,

deal_id country mn13 nl mn14 gb mn15 mn28 ca

you can utilize cross apply this:

select d.*, dc.* deals d cross apply (select top 1 dc.* deals_country dc d.deal_id = dc.deal_id ) dc order d.deal_id;

sql sql-server