How to avoid duplicate records in SQL joins

Question

0.00/5 (No votes)

See more:

i have biodata table which have morethan 20 rows but here it showing only 4 row records ??

What I have tried:

SQL

SELECT  DISTINCT    
  d1.emp_code,
  d1.emp_name, 
  dbo.Setup_designation.des_name, 
  dbo.Setup_Department.Depart_name, 
  dbo.Transaction_Employee.joining_date, 
  dbo.Employee_Salary_Increment.current_salary, 
  d3.type, 
  d3.amount, 
  d2.r_casual_leave, 
  d2.r_sick_leave, 
  d2.r_annual_leave, 
  d2.r_extra_leave, 
  d2.others_leave
FROM         
  dbo.Employee_Biodata d1 
  INNER JOIN dbo.Transaction_Employee ON d1.Emp_bio_id = dbo.Transaction_Employee.Emp_bio_id 
  INNER JOIN dbo.Setup_designation ON dbo.Transaction_Employee.des_id = dbo.Setup_designation.des_id 
  INNER JOIN dbo.Setup_Department ON dbo.Transaction_Employee.Department_id = dbo.Setup_Department.Department_id 
  INNER JOIN dbo.Employee_Salary_Increment ON d1.Emp_bio_id = dbo.Employee_Salary_Increment.Emp_bio_id 
  INNER JOIN dbo.Loan_Issue d3 ON d1.Emp_bio_id = d3.Emp_bio_id 
  INNER JOIN dbo.Leave_issue d2 ON d1.Emp_bio_id = d2.Emp_bio_id where d1.Emp_bio_id = d3.Emp_bio_id and d1.Emp_bio_id = d2.Emp_bio_id

Posted 1-Aug-17 20:57pm

Ali Khan

Updated 1-Apr-20 23:35pm

Kornfeld Eliyahu Peter

v3

Add a Solution

Comments

RickZeeland 2-Aug-17 3:21am

We can only guess if we do not have the data, please add example data e.g. a script with CREATE TABLE, INSERT INTO statements.
Or even better: create an online example on http://sqlfiddle.com/

Ali Khan 2-Aug-17 5:10am

ok

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Accepted Answer · 2017-08-02T00:43:00

Without your data I'll need to guess.

You're using INNER JOIN - which means no record returns if it fails to find a match. It is very common, therefore, to return few than all of your rows - especially with so many joins, each having the potential to eliminate some rows.

You should use LEFT JOIN or RIGHT JOIN in appropriate locations, instead, which will return a NULL for unmatched records - but will return all of them. Use ISNULL() if you wish to have a value other than NULL for non-matches.

Now, particularly without any tables to view, I couldn't suggest which joins should be changed and to what their ON qualifier should be to make the proper match. It is important you link the table by the correct fields.

The above is based upon your Question, not your Subject Line.