How to join 3 tables using SQL code in C# - Windows Forms

Question

I have 3 tables in my SQL database (EmployeeMaster, TripPayroll, Deduction).

I need to reflect in the 1st 3 columns of my datagrid the IDNo, LastName, FirstName of all employees in EmployeeMaster. Then in the 4th column of the datagrid, I want to sum the amount from TripPayroll of the IDNo of employee in the 1st column. The code should sum the amount from TripPayroll with the same IDNo as in the 1st Column. Then in the 5th column of the Datagrid, I want to sum all the amount from Deduction Table of the with the same IDNo as in the 1st column but if the IDNo has no corresponding IDNo in the Deduction Table, the value is 0.00. then in the 6th column, I want to get the difference by subtracting the Amount in TripPayroll - Deduction.

string cs = ConfigurationManager.ConnectionStrings["DbaseConnection2"].ConnectionString; SqlConnection con = new SqlConnection(cs); con.Open();

            SqlDataAdapter da = new SqlDataAdapter("SELECT EmployeeMaster.IDNo, EmployeeMaster.LastName, EmployeeMaster.FirstName, COUNT(TripPayroll.DTRNo) AS No_Trip, SUM(TripPayroll.Rate) AS Gross_Pay, ISNULL(SUM(Deduction.Amount),'0.00') AS Deduction, SUM(TripPayroll.Rate)-ISNULL(SUM(Deduction.Amount),'0.00') AS Total_Pay FROM EmployeeMaster LEFT JOIN TripPayroll ON EmployeeMaster.IDNo = TripPayroll.IDNo LEFT JOIN Deduction ON EmployeeMaster.IDNo=Deduction.IDNo WHERE (TripPayroll.DeliveryDate <= '" + PayrollCutOff.Text + "') AND (TripPayroll.Status = 'Complete') GROUP BY EmployeeMaster.LastName, EmployeeMaster.FirstName,EmployeeMaster.IDNo", con);
            DataSet ds = new DataSet();
            da.Fill(ds);


            Payroll_List.DataSource = ds.Tables[0];

Thank you.

What RDBMS are you using? Please add a relevant tag. Also: please post the table structure so that we have something to go on... — marc_s
looking like you got a homework to do. At least provide ERD of the scenario. — Saeed ur Rehman

Saeed ur Rehman Saeed ur Rehman · Accepted Answer · 2016-09-01T06:23:47

select Em.IDNo,Em.LastName,Em.FirstName,Sum(TP.Amount),IsNUll(Sum(dc.Amount),0),(Tp.Amount - Dc.Amount) as AmountDiff
from EmployeeMaster Em
Inner join TripPayroll TP 
on Em.IDNo = TP.IDNo
Inner join Deduction dc
on EM.IDNo = dc.IDNo

Try this. Hope You will get some idea from this solution.

How to join 3 tables using SQL code in C# - Windows Forms

1 Answers