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

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

My data:


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.


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;

select mstr.Emp_cd, mstr.Emp_Name, isnull(sum(det.num_dy), 0) as num_day
from dbo.Employee_Mstr mstr
left join dbo.Employee_Nums det on (mstr.Emp_cd = det.Emp_cd)
group by mstr.Emp_cd, mstr.Emp_Name;

should do the job.

Here it's results of SQL execution:

Emp_cd      Emp_Name
----------- --------------------------------------------------
1           A
2           B
3           C

(3 row(s) affected)

Emp_cd      num_dy
----------- -----------
3           4
3           2

(2 row(s) affected)

Emp_cd      Emp_Name                                           num_day
----------- -------------------------------------------------- -----------
1           A                                                  0
2           B                                                  0
3           C                                                  6
Warning: Null value is eliminated by an aggregate or other SET operation.

(3 row(s) affected)

Try this:

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

Make pk emp_cd the primary key, and then join the tales using left join. You can then use sumof to add values, use groupby before adding the values.

If you want, just check

Need Your Help

Mahout Datamodel with duplicate user,item enteries but different preference values

hadoop mahout recommendation-engine mahout-recommender

I was wondering how the distributed mahout recommender job handled csv files where duplicate and triplicate user,item entries exist but with

Piping Replace to New File


$x = Get-Content($file)