complex query - need help

I have three tables-

MemberTable (primary key: memberID),
MemberDataTable (PK: DataID),
CompanyTable(PK: CompID)

The MemberTable contains name,address of members along with companyID (CompID)
The CompanyTable contains list of all comapny name along with CompID
The memberData table contains multiple rows for each memberID in memberTable.

I want to write a query which will give me the latest entry in memberDataTable for every member of a particular company. How do I write the query?

For eg, if I want compID=1, i need to get:
Select max(DataID) from memberdatatable where memberID=__
I would have to repeat the above for every member in the company with companyID=1

Any help in forming a proper query would be greatly appreciated

One way to do this is:

Code Snippet

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1
insert into @.MemberDataTable select 2,1
insert into @.MemberDataTable select 3,2
insert into @.MemberDataTable select 4,1
insert into @.MemberDataTable select 5,2

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID

|||What about:

SELECT MAX(DataId), CompId
From MemberDataTable
Group by CompId

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply Mobin

but I still have a problem. I have more data in the memberdatatable which i need to retrieve along with the last row. When i add these data fileds: data1, data2, it gives me duplicate values of memberID.

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int,data1 int, data2 int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1,9,9
insert into @.MemberDataTable select 2,1,9,8
insert into @.MemberDataTable select 3,2,8,9
insert into @.MemberDataTable select 4,1,8,8
insert into @.MemberDataTable select 5,2,9,8

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID,data1,data2

Another thing i want to know is why does the query show an error when data1, data2 are fetched without being written in group by clause
|||Thanks Jens, but :
MemberDataTable doesn't have CompId.
|||I just modified what Mobin sent and this worked:

select * from @.memberdatatable where dataid in (select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID)

Mobin, I'd really appreciate it if u could explain why the query showed an error when i tried to fetch data1, data2 without listing it in the groupby clause as in:
select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID
|||

The following query may help you..

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

|||

hi

error is caused since the fields data1 and data2 are not mentioned in the group by clause. the following query will fix the problem:

Code Snippet

select * from @.MemberDataTable where DataID in(
select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyIDmailto:companyID=@.companyID">companyID=@.companyID</A< A>> ) group by memberID)

regards