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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts