Code Snippet
Create Table #companytable (
[CompID] Varchar(100) ,
[name] Varchar(100)
);
Insert Into #companytable Values('1','Comp1');
Insert Into #companytable Values('2','Comp2');
Insert Into #companytable Values('3','Comp3');
Insert Into #companytable Values('4','Comp4');
Insert Into #companytable Values('5','Comp5');
Create Table #membertable (
[memberID] Varchar(100) ,
[name] Varchar(100) ,
[address] Varchar(100) ,
[companyID] Varchar(100)
);
Insert Into #membertable Values('1','Mem1','Address1','1');
Insert Into #membertable Values('2','Mem2','Address2','3');
Insert Into #membertable Values('3','Mem3','Address3','2');
Insert Into #membertable Values('4','Mem4','Address4','2');
Insert Into #membertable Values('5','Mem5','Address5','1');
Insert Into #membertable Values('6','Mem6','Address6','2');
Insert Into #membertable Values('7','Mem7','Address7','2');
Insert Into #membertable Values('8','Mem8','Address8','3');
Create Table #memberdatatable (
[memberID] Varchar(100)
);
Insert Into #memberdatatable Values('1');
Insert Into #memberdatatable Values('4');
Insert Into #memberdatatable Values('5');
Insert Into #memberdatatable Values('8');
Select * From #membertable Main
Join #companytable Comp On Comp.CompID= Main.companyID
Join (Select Max(MD.memberID) memberID From #memberdatatable MD
Join #membertable MT On MD.memberID = MT.memberID Group BY companyID) as Data
On data.memberID=Main.memberID