CODEWITHSUNDEEP
sql-servervb.net
LeoJb
LeoJb

Reputation: 25

I want to get result for all employees

I have the following code, but I don't get the true result. I'm getting only the result for one employee, and what I want is for all employees in my tables.

The code is:

Select * From
     (Select EmpNo, EmpEName 
      from EmployeeMainFile 
      Group By EmpNo, EmpEName) s2
  INNER Join 
     (Select DISTINCT EmpSNo,EmpSTotalSalary 
      from EmpSalary where ID = 
          (Select Max(ID) from EmpSalary) 
           Group by EmpSNo,EmpSTotalSalary) s1
    on s1.EmpSNo = s2.EmpNo

Upvotes: 0

Views: 86

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Because the subquery expression Select Max(ID) from EmpSalary only returns one ID for the entire set of salaries. You need the Max(ID) for each Employee.

If IDs are sequentially increasing, and greatest value of ID (for each Employee) represents the latest, current, salary for that employee, then:

Select e.EmpNo, e.EmpEName, Max(EmpSTotalSalary) MaxSalary
From EmployeeMainFile e
    Join EmpSalary s
       on s.EmpSNo = e.EmpNo
          and s.ID = (Select Max(ID) 
                    from EmpSalary 
                    Where EmpSNo = s.EmpSNo)
Group By e.EmpNo, e.EmpEName

Upvotes: 1

LONG
LONG

Reputation: 4610

Select * From
     (Select EmpNo, EmpEName 
      from EmployeeMainFile 
      Group By EmpNo, EmpEName) s2
  INNER Join 
     (Select EmpSNo,Max(EmpSTotalSalary) from EmpSalary 
           Group by EmpSNo) s1
    on s1.EmpSNo = s2.EmpNo

UPDATED ANSWER

Select * From
     (Select EmpNo, EmpEName 
      from EmployeeMainFile 
      Group By EmpNo, EmpEName) as s2
  INNER Join 
     (
      SELECT * FROM 
      (
      Select EmpSNo,ROW_NUMBER()OVER(PARTITION BY EmpSNo ORDER BY TIMESTAMP DESC) as Latest
      from EmpSalary 
       ) AS ABC
       WHERE ABC.Latest =1 ) as s1
    on s1.EmpSNo = s2.EmpNo

Upvotes: 0

Related Questions