 <p>try,</p> <pre><code>declare @t table(CarName varchar(10),Colour varchar(10), Size varchar(10)) insert into @t (carname,Colour,Size) values ('Car 1', 'Red', 'big'), ('car 2','Blue','small'), ('car 3','Gr een','small') declare @t1 table(CarName varchar(10),part varchar(10)) insert into @t1 (carname,part) values ('car 1','123456'), ('car 1','2345670'), ('car 1','345678'), ('car 2','ABCDEFG'), ('car 2','BcDEFGH'), ('car 2','cDEFGHI') select t.*, t1.part from @t t right join (select carname,part, row_number() over (partition by carname order by part) rno from @t1) t1 on t.CarName=(case when t1.rno=1 then t1.CarName else null end) </code></pre>