How to get all rows from left table in a join statement?

Question

I have two tables. One is Employee_Mstr and other is EmployeeLeaveRequest_mstr.

My data:

Solution 1

SELECT tab1.emp_cd, tab1.emp_name, SUM(tab2.num_dy)
FROM Employee_Mstr tab1, "2nd Table Records" tab2
where tab1.emp_cd = tab2.emp_cd
group by tab1.emp_cd, tab1.emp_name;

Should work, just set the correct table name for the 2nd table.

Solution 2

Use GROUP BY.

select t1.emp_cd, t1.emp_name, sum(t2.num_dy) from 
  Employee_Mstr t1 left join 
  EmployeeLeaveRequest_mstr t2 on t1.emp_cd = t2.emp_cd group by t1.emp_cd, t2.emp_name;

Comments

[Yusubov]

let us know what did you get, pick the answers please

[Tripati Subudhi]

I tried this but this is giving me only Emp_cd 3 records but not 1 and 2 as I need all from left table.

[Tripati Subudhi]

I tried this but this is giving me only Emp_cd "3" records but not 1 and 2 as I need all records from left table.

[Csaba Benko]

I updated the solution, with run results on MS SQL. At least it worked fine for me as you can see.

[Shahbaz]

@CsabaBenko, why are you editing someone else's answer when you have your own answer to this question?!

[Tripati Subudhi]

Even this one is also working but I cant mark your answer as only one we can mark as answer... Thanks for your response...

[wroniasty]

@TripatiSubudhi sure, no problem. You can upvote it though ;)

[Flashpix]

If you need all results from the left table we need a left join. SELECT tab1.emp_cd, tab1.emp_name, SUM(tab2.num_dy) FROM Employee_Mstr tab1 LEFT JOIN "2nd Table Records" tab2 ON tab1.emp_cd = tab2.emp_cd group by tab1.emp_cd, tab1.emp_name;