Click here to Skip to main content
14,732,816 members
Please Sign up or sign in to vote.
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
Updated 9-Mar-19 10:27am
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

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.


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 
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900