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:

Answers


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.


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;

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 t1.cd, t1.name, sum(t2.num_dy) from employee_Mstr t1 
left join EmployeeLeaveRequest_mstr t2 on t1.cd = t2.cd group by t1.cd, t1.name;

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 w3school.com.


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 org.apache.mahout.cf.taste.hadoop.item.RecommenderJob handled csv files where duplicate and triplicate user,item entries exist but with

Piping Replace to New File

powershell

$x = Get-Content($file)