加入2个以上表时如何避免重复 [英] How to avoid duplication when Joining more than 2 tables

问题描述

嗨


我必须从3个表中检索必填字段，并且所有3个表中的公共字段是NRIC。当我使用下面的查询时，我得到重复。

 选择  distinct  A._qsn，A._queueno，A._ NRICNo，B._ Name，B._race，B._block，B._ unitno，B._street，B。 _handphone，B._gender，B._race，B._language，C._letterNo，A._status，A._ wentHome，A._remarks，A._caseType，A._welfare，A._welfareSave 来自 tbQueue A，tbPeopleResident B，tbLetterHistory C 其中 A._NRICNo = B._NRICNo 和 B._NRICNo = C._NRICNo 

您的建议将会受到赞赏。

解决方案

  SELECT  
 A._qsn，A._queueno，A._ NRICNo，B._ Name，B._race，B._block，B._unitno，B._street，B._handphone，B._gender，B._race，B._language， C._letterNo，A._status，A._ wentHome，A._remarks，A._caseType，A._welfare，A._ we lfareSave 
 来自 tbQueue A 
  INNER   JOIN  tbPeopleResident B  ON  A._NRICNo = B._NRICNo 
  INNER  < span class =code-keyword> JOIN  tbLetterHistory C  ON  A._NRICNo = C._NRICNo 

如果您的数据存储在数据库中，那么您永远不需要在查询中使用 DISTINCT 关键字。

如果表B或C有多个值，那么每个NRICNo将获得多行，但所有字段都不是唯一的，因为表B或C将具有不同的值。

由于表之间未定义连接，您将获得重复数据：来自tbQueue A，tbPeopleResident B，tbLetterHistory C



尝试用以下内容替换您的查询：

  SELECT   DISTINCT  A._qsn，A._queueno，A._ NRICNo，B._ Name，B._race，B._block，B._unitno ，B._street，B._handphone，B._gender，B._race，B._language，C._letterNo，A._status，A._ wentHome，A._remarks，A._caseType，A._welfare，A._welfareSave 
  FROM  tbQueue  AS  A  LEFT  < span class =code-keyword> JOIN  tbPeopleResident  AS  B  ON  A._NRICNo = B._NRICNo  LEFT   JOIN  tbLetterHistory  AS  C  ON  B._NRICNo = C._NRICNo 

您需要测试它使用不同类型的连接： LEFT ， RIGHT ， INNER （使用OUTER选项），但首先请参考以下链接：

T-SQL基础：不同类型的连接 [ ^ ]

T-Sql加入（w3schools） [ ^ ]

T-SQL联接（msdn） [ ^ ]

使用联接（msdn） [ ^ ]

和最好的;）

视觉表现SQL联接 [ ^ ]

Hi
I have to retrieve required field from 3 tables and the common field is NRIC in all the 3 tables. when I am using the below query I am getting duplication.

select distinct A._qsn,A._queueno,A._NRICNo,B._Name,B._race,B._block,B._unitno,B._street,B._handphone,B._gender,B._race,B._language,C._letterNo,A._status,A._wentHome,A._remarks,A._caseType,A._welfare,A._welfareSave from tbQueue A, tbPeopleResident B ,tbLetterHistory C where A._NRICNo=B._NRICNo and B._NRICNo = C._NRICNo

Your suggestion''s will be appreciated.

解决方案

Hi,

SELECT
 A._qsn,A._queueno,A._NRICNo,B._Name,B._race,B._block,B._unitno,B._street,B._handphone,B._gender,B._race, B._language, C._letterNo, A._status ,A._wentHome, A._remarks, A._caseType, A._welfare, A._welfareSave
from tbQueue A
    INNER JOIN tbPeopleResident B ON A._NRICNo=B._NRICNo
    INNER JOIN tbLetterHistory C ON A._NRICNo=C._NRICNo

If your data is proper stored in database then you never need to use DISTINCT keyword in your query.
If your table B or C has multiple values then you will get more then one row for each NRICNo but all fields will not be unique because table B or C will have different values.

You''re getting duplicated data because of not defined joins between tables: from tbQueue A, tbPeopleResident B ,tbLetterHistory C

Try to replace your query with:

SELECT DISTINCT A._qsn, A._queueno, A._NRICNo, B._Name, B._race, B._block, B._unitno, B._street, B._handphone, B._gender, B._race, B._language, C._letterNo, A._status, A._wentHome, A._remarks, A._caseType, A._welfare, A._welfareSave
FROM tbQueue AS A LEFT JOIN tbPeopleResident AS B ON A._NRICNo=B._NRICNo LEFT JOIN tbLetterHistory AS C ON B._NRICNo = C._NRICNo

You need to test it with defferent types of joins: LEFT, RIGHT, INNER (with OUTER option), but first, please, refer the below links:
T-SQL Basics: Different Types of Joins[^]
T-Sql Joins (w3schools)[^]
T-SQL Joins (msdn)[^]
Using Joins (msdn)[^]
and the best of all ;)
Visual Representation of SQL Joins[^]

这篇关于加入2个以上表时如何避免重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！