How to avoid duplication when Joining more than 2 tables

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

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.

SQL

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.

Posted 24-May-13 22:55pm

Lancy.net

Add a Solution

2 solutions

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)

MaulikDusara · Answer 1 · 2013-05-25T01:15:00

Hi,

SQL

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.

Maciej Los · Answer 2 · 2013-05-24T23:52:00

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:

SQL

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[^]

How to avoid duplication when Joining more than 2 tables

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0