How to remove duplicate record from results doing left join in SQL

Question

0.00/5 (No votes)

See more:

Quote:
I have three tables User Table,Enquiry table and Activity Table .when I do a inner and LEFT JOIN, I am getting duplicate records because of NULL values.

User Table -1

user_id | user_firstName | user_lastName

1 | Joe | Smith

2 | John | Doe

3 | Robert | Smith

Enquiry Table -2

EnquiryID | CreatedBy| |
1 | 1 |
2 | 1 |

Activiy Table - 3

ActivityID | CreatedBy| AssignedBy| AssignedTO|

1 | 1 | null | null |

2 | 1 | 2 | 3 |

Expected Output of all three combining result is

Enquiry ID | | CreatedBy| AssignedBy| AssignedTO|

1 | Joe | null | null |

2 | Joe | John | Rober |

SQL:

SELECT DISTINCT E.EnquirdID as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY , U2,FirstName as AssignedTO
FROM Enquiry E inner join User U on E.UserID = U.UserID
inner join Activity A on E.Enquiry = A.EnquiryID
Left Join User U1 on A.AssignedBY = U1.UserID
Left Join User U2 on A.AssignedTO = U2.UserID

I am getting duplicate Enquiry record from above query even though using Distinct for EnquiryID

What I have tried:

SELECT DISTINCT E.EnquirdID as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY  , U2,FirstName as AssignedTO
FROM Enquiry E inner join User U on E.UserID = U.UserID
               inner join Activity A on E.Enquiry = A.EnquiryID
               Left Join User U1  on A.AssignedBY = U1.UserID
               Left Join User U2 on  A.AssignedTO = U2.UserID

I am getting duplicate Enquiry record  from above query even though using Distinct for EnquiryID

Posted 9-Mar-19 5:33am

Member 10660835

Updated 9-Mar-19 9:27am

Add a Solution

Comments

F-ES Sitecore 9-Mar-19 11:59am

Not sure I fully understand the question, but if you don't want the null results then use a "join" rather than "left join"

Member 10660835 9-Mar-19 12:01pm

i am getting duplicate records with my query which i mentioned with my above data..

how to avoid duplicate record

MadMyche 9-Mar-19 15:48pm

If you use an INNER JOIN then it wont return the NULL results.
Or you could add in WHERE (u1.UserID NOT NULL)

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)

Bryian Tan · Accepted Answer · 2019-03-09T09:27:00

The posted code seem ok, maybe you didn't post the correct table content/schema for us to replicate the issue. Below is an example using the posted data and modify schema to match the query and they all produce the same results. My take is that maybe you didn't post enough information or your SQL query join using wrong column. Is tough to tell because your provided query doesn't match the schema.

SQL

DECLARE @user TABLE (
	UserID INT,
	FirstName  VARCHAR(50),
	LastName  VARCHAR(50)
)

DECLARE @Enquiry TABLE (
	EnquiryID  INT,
	CreatedBy  INT
)

DECLARE @Activity TABLE (
	ActivityID   INT,
	CreatedBy  INT,
	AssignedBy  INT NULL,
	AssignedTO  INT NULL,
	EnquiryID  INT
)

INSERT INTO @user
	SELECT 1,'Joe','Smith' UNION
	SELECT 2,'John ','Doe' UNION
	SELECT 3,'Robert ','Smith'

INSERT INTO @Enquiry
	SELECT 1,1 UNION
	SELECT 2,1

INSERT INTO @Activity
	SELECT 1,1,NULL,NULL,1 UNION
	SELECT 2,1,2,3,2

--without using Enquiry table
SELECT a.EnquiryID, cb.FirstName, ab.FirstName, at.FirstName FROM @Activity a 
	JOIN @user cb ON a.CreatedBy = cb.UserID
	LEFT JOIN @user ab ON a.AssignedBy = ab.UserID	
	LEFT JOIN @user at ON a.AssignedTO = at.UserID	

--using Enquiry table
SELECT e.EnquiryID, eu.FirstName 'CreatedBy' ,ab.FirstName 'AssignedBy',  at.FirstName 'AssignedTO'
	FROM @Enquiry e
	JOIN @user eu ON e.CreatedBy = eu.UserID
	JOIN @Activity a ON e.EnquiryID = a.EnquiryID
	LEFT JOIN @user ab ON a.AssignedBy = ab.UserID	
	LEFT JOIN @user at ON a.AssignedTO = at.UserID	

--Using your query with small modification to the table schema
SELECT DISTINCT E.EnquiryId as Enquiry,U.FirstName as CreatedBY ,U1.FirstName as AssignedBY , U2.FirstName as AssignedTO
FROM @Enquiry E inner join @user U on E.CreatedBy = U.UserID
inner join @Activity A on E.EnquiryId = A.EnquiryID
Left Join @user U1 on A.AssignedBY = U1.UserID
Left Join @user U2 on A.AssignedTO = U2.UserID

EnquiryID	FirstName	FirstName	FirstName
1	Joe	NULL	NULL
2	Joe	John 	Robert