how to innerjoin with max id in another table [Resolved]

Posted by Amritha444 under Sql Server on 7/1/2011 | Points: 10 | Views : 7364 | Status : [Member] | Replies : 2
hi all

i have two tables employee and employee status ..for each employee in employee table have number of entries in employee status table..while innerjoin more than one row comes for each employee.i want each employee in employee table inner join with maximum id employee statuis entry of that particular employee..

How to do that

Thanks in Advance




Responses

Posted by: PandianS on: 7/1/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi
Try this...
Select E.EmpName, St.StatusInfo from Employee E Join

(Select MAX(EmployeeStatusID) EmployeeStatus,EmployeeID From EmployeeStatus Group by EmployeeID) St
On (E.EmployeeID = St.EmployeeID)
Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 7/1/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi
IF two tables are like these
1.Employee
EmpID    EmpName

1 Narayan
2 Debata

2.EmployeeStatus
StatusID EmpID StatusName

1 1 Active
2 2 Blocked
3 1 Blocked
4 2 Active


Then Your queery will be something like this
Select * from Employee EMP

INNER JOIN
(
Select MAX(StatusID) StatusID,EmpID from EmployeeStatus as EMPST
GROUP BY EMPST.EMPID
) as X ON X.EmpID=EMP.EmpID


Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response