CODEWITHSUNDEEP
sqlvb.netms-access
MichaelXX
MichaelXX

Reputation: 29

Unwanted Duplicate Values SELECT JOIN

I have 2 records in 2 tables with the same ID , I'm getting duplicates values when it loads from DB

so here's my first table: 

 |--ID--|--Gross Salary--|--Day--|
     1      100             1
     1      200             2

table 2:

|--ID--|--Net Salary--|
     1      100          
     1      200

Result when loads:

  |--ID--|--Gross Salary--|--Net Salary--|--Day--|
        1      100            100           1
        1      100            200           1
        1      200            100           2
        1      200            200           2

What i need to be displayed:

|--ID--|--Gross Salary--|--Net Salary--|--Day--|
     1      100             100            1
     1      200             200            2

My code:

  Dim loadq As String = "SELECT DriverEmpEarns.LastName, DriverEmpEarns.FirstName, DriverEmpEarns.Mi, DriverEmpEarns.[Position], DriverEmpEarns.[No of Trips 6W], DriverEmpEarns.[Total 6w], DriverEmpEarns.[No of Trips 4W], DriverEmpEarns.[Total 4w], DriverEmpEarns.[SHR], DriverEmpEarns.[PR], DriverEmpEarns.[Additional Incentives], DriverEmpEarns.[SpecialAllowance], DriverEmpEarns.GrossSalary, " & _
                "DriverEmpDed.WHTax, DriverEmpDed.PhilHealth, DriverEmpDed.SSS, DriverEmpDed.Vale, DriverEmpDed.Advances, DriverEmpDed.Damages, DriverEmpDed.Pondo, DriverEmpDed.StoreCanteen, DriverEmpDed.Others, DriverEmpDed.NetSalary  " & _
                "FROM (DriverEmpDed LEFT JOIN DriverEmpEarns ON DriverEmpEarns.EmpPID = DriverEmpDed.EmpPID) " & _
                "WHERE [Pday1] >= # " & MetroDateTime1.Value.ToShortDateString & "# AND [Pday2] <= #" & MetroDateTime2.Value.ToShortDateString & "# AND DriverEmpEarns.EmpPID = DriverEmpDed.EmpPID"

Upvotes: 0

Views: 48

Answers (2)

xQbert
xQbert

Reputation: 35323

They are not duplicates. This is HOW SQL works. You asked the tables to join based on the EmployeeId. Since the same ID exists in both tables twice, you get 2x2 records (4). Remove 1 record from one of the tables and you'd get just 2 records. You either need to remove the second records, devise a rule which governs what net and gross salary relate when the same employeeID exists more than once, or re-architect the data so that a join isn't needed.

Just a few things:

SELECT DriverEmpEarns.LastName, 
       DriverEmpEarns.FirstName, 
       DriverEmpEarns.Mi, 
       DriverEmpEarns.[Position], 
       DriverEmpEarns.[No of Trips 6W], 
       DriverEmpEarns.[Total 6w], 
       DriverEmpEarns.[No of Trips 4W], 
       DriverEmpEarns.[Total 4w], 
       DriverEmpEarns.[SHR], 
       DriverEmpEarns.[PR], 
       DriverEmpEarns.[Additional Incentives], 
       DriverEmpEarns.[SpecialAllowance], 
       DriverEmpEarns.GrossSalary,
       DriverEmpDed.WHTax, 
       DriverEmpDed.PhilHealth, 
       DriverEmpDed.SSS, 
       DriverEmpDed.Vale, 
       DriverEmpDed.Advances, 
       DriverEmpDed.Damages, 
       DriverEmpDed.Pondo, 
       DriverEmpDed.StoreCanteen, 
       DriverEmpDed.Others, 
       DriverEmpDed.NetSalary 
       FROM DriverEmpDed 
       LEFT JOIN DriverEmpEarns 
         ON DriverEmpEarns.EmpPID = DriverEmpDed.EmpPID 
       WHERE [Pday1] >= # " & MetroDateTime1.Value.ToShortDateString & "# 
         AND [Pday2] <= #" & MetroDateTime2.Value.ToShortDateString & "# 
         AND DriverEmpEarns.EmpPID = DriverEmpDed.EmpPID <--Redundant
  1. You don't need the last "AND" in the where clause AND DriverEmpEarns.EmpPID = DriverEmpDed.EmpPID" should be removed
  2. Without knowing the table [Pday1] and [Pday2] reside, you may be negating your left join.
  3. The reason you're getting multiple records in the result is because the data has 2 records in each table being joined that match. T1.record1 goes with t1 records 1 & 2. T1.record2 goes with t2. records 1 and two. Thus 2x2 result in 4 records. You need another criteria to define which net salary goes with which gross salary. Without this, you can't limit the data better.

Upvotes: 0

G B
G B

Reputation: 1462

your table2 also needs to have a column for day and then you will include that in your join clause.

Upvotes: 1

Related Questions