What is wrong with this sql innerjoin?

Question

Here is my select statement with the innerjoin of two tables,

if not exists(select EmpId from SalaryDetails 
              where EmpId in (select Emp_Id 
                              from Employee where Desig_Id=@CategoryId))
begin
  // some statements here
end
else
begin
  SELECT e.Emp_Id, e.Identity_No, e.Emp_Name,
      case WHEN e.SalaryBasis=1 THEN 'Weekly' 
           ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
      (SELECT TOP 1 RemainingAdvance 
       FROM SalaryDetails 
       ORDER BY CreatedDate DESC) as Advance
    FROM Employee as e inner join Designation as d on e.Desig_Id=d.Desig_Id 
    INNER JOIN SalaryDetails as S on e.Emp_Id=S.EmpId 
End

My results pane,

And My SalaryDetails Table,

EDIT: My Output must be,

16 CR14 Natarajan Weekly 150.00 354.00
17 cr12333 Pandian Weekly 122.00 0.00

Answer 1

You're not filtering the sub-query (SELECT TOP 1 RemainingAdvance FROM SalaryDetails ORDER BY CreatedDate DESC) on any employee ID, so it's giving you the first record in the entire table when sorted by CreatedDate DESC (which I'm guessing is 354.)

You will probably want to move that table expression into your FROM clause, not your SELECT, include your employee ID, and do a join on that expression.

SELECT 
    e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1 THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
    from Employee as e inner join Designation as d on e.Desig_Id=d.Desig_Id 
    inner join SalaryDetails as S on e.Emp_Id=S.EmpId 
    inner join
    (SELECT EmpID, RemainingAdvance, RANK() OVER (PARTITION BY EmpID ORDER BY CreatedDate DESC) AS SalaryRank FROM SalaryDetails ORDER BY CreatedDate DESC) as Advance ON Advance.EmpID = e.Emp_ID AND Advance.SalaryRank = 1

This is just off the top of my head so may take a bit of tweaking to run correctly. Note also the use of the RANK() function - if you use TOP 1, you're only ever getting the first record of the entire table. What you need is the first record per employee ID.

If this was me I would probably make that table expression a view or even a scalar-valued function taking your employee ID and returning the first RemainingAdvance value, then you could use TOP 1 and filter on the employee ID.

Answer 2

It looks like your join to Designation isn't even used and you're also missing your WHERE clause that you used in the IF statement at the top. I'd also move the subquery down into the join like Andy pointed out. Without having the DB to test against this probably won't be exact but I'd rewrite it to something like;

SELECT e.Emp_Id, e.Identity_No, e.Emp_Name,
  case WHEN e.SalaryBasis=1 
       THEN 'Weekly' 
       ELSE 'Monthly' end as SalaryBasis,
  e.FixedSalary,S.RemainingAdvance as Advance
FROM Employee as e 
  INNER JOIN (
   SELECT TOP 1 EmpId, RemainingAdvance 
   FROM SalaryDetails 
   ORDER BY CreatedDate DESC) as S on e.Emp_Id=S.EmpId 
WHERE e.Desig_Id=@CategoryId

Andy's suggestion to move the subquery into a view is a good one, much easier to read and probably a lot more efficient if the DB is large.

EDIT: (ANSWER)

(SELECT sd.empid,
               sd.remainingadvance,
               ROW_NUMBER() OVER (PARTITION BY sd.empid ORDER BY sd.createddate DESC) AS rank
          FROM SALARYDETAILS sd
          JOIN EMPLOYEE e ON e.emp_id = sd.empid
                         AND e.desig_id = @CategoryId) s
            WHERE s.rank = 1

I edited jay's answer because he came close to my output...