如何避免SQL连接中的重复记录 [英] How to avoid duplicate records in SQL joins

问题描述

我有biodata表，它有20多行，但这里只显示4行记录？



我尝试过：

  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 其中 d1.Emp_bio_id = d3.Emp_bio_id 和 d1.Emp_bio_id = d2.Emp_bio_id 

解决方案

没有您的数据我需要猜测。



您正在使用INNER JOIN - 这意味着如果找不到匹配则没有记录返回。因此，返回的行数很少，而不是所有的行 - 特别是有很多连接，每个连接都有可能消除一些行。



你应该使用相反，LEFT JOIN或RIGHT JOIN会在不匹配的记录中返回NULL，但会返回所有记录。如果你希望非匹配的值不是NULL，请使用ISNULL（）。



现在，特别是没有任何表可供查看，我无法建议应该更改连接以及它们的ON限定符应该是什么来进行正确的匹配。重要的是你用正确的字段链接表格。



以上是基于你的问题，而不是你的主题。

< BR>

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

What I have tried:

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 

解决方案

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.

这篇关于如何避免SQL连接中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！