SQLTeam.com | Weblogs | Forums

Help with formatting an WHERE clause to remove duplicates without using DISTINCT

sql2012

#1

Hi everyone. I am trying to make a selection from a set of tables, and I need to see the results when the Primary Contact is equal to (X username) OR when the Inside Sales Rep is equal to (X username) AND when its both, but when its both, i get duplicate values because it will show up twice (once for being the Inside Sales Rep, and once for being the Primary Contact). I am trying to refine the logic so that I only see a result when it is one or the other, and just ONE result from when it's both.

Here is the code

SELECT sr.servicerequestid, pc.ContactId , sr.InsideSalesRepId, sr.CompletedDate
From dbo.ServiceRequests sr
join dbo.StructureServiceRequests ss on sr.ServiceRequestId = ss.ServiceRequestId
join dbo.Structures st on ss.StructureId = st.StructureId
join dbo.Projects pr on st.ProjectId = pr.ProjectId
join dbo.ProjectContacts pc on pr.ProjectId = pc.ProjectId
join dbo.Users u on pc.ContactId = u.UserId
where pc.ContactId = 4784
or sr.InsideSalesRepId = 4784

So in that result set, you can see it returns multiple servicerequests for each result where either the InsideSalesRepId is X or the ContactId is X, and returns duplicates when its both.

Ideally I'd like to return DISTINCT servicerequestids, but the results are being selected as the entire table, and I am not going to spend all my time going through to individually group by every single column (this is already being used in the MVC this way).

I really hope this made sense, but I'd be happy to clarify any other questions. I've spent a lot of time trying to figure out this logic so I hope someone out there can help me out! Thanks


#2

Instead of the results you posted, what is the result you are trying to get? I see there are two rows with servicerequestid = 199802 (rows 4 and 5). Do you want to get only one row for that servicerequestid? If so, what should show in the contactid column?


#3

I translated the actual code from the MVC, which was in Linq, to sql, and its sliiightly different because I altered it to best show what I was talking about. In the result set in the MVC, the entire table is being selected, not just those specific columns. What I would want is, for ONE servicerequestid to return when there are matching InsideSalesRepIds (for instance, lines 3, 4, & 5 which all have InsideSalesRepId of 4784 would all just return one single 199802 servicerepid).

lines 16-20, which all have servicerepid of 242788, would only return once. even taking into account that for one result (16), BOTH the ContactId and InsideSalesRepId is 4784.


#4

Perhaps this? Do you need to get just one column in the output, or do you want the other columns as well? In the following, the query inside the WHERE EXISTS clause is your current query with an additional condition added in the WHERE clause.

SELECT 
	sro.servicerequestid
FROM
	dbo.ServiceRequests sro
WHERE EXISTS
(
	SELECT *
	FROM    dbo.ServiceRequests sr
			JOIN dbo.StructureServiceRequests ss ON sr.ServiceRequestId = ss.ServiceRequestId
			JOIN dbo.Structures st ON ss.StructureId = st.StructureId
			JOIN dbo.Projects pr ON st.ProjectId = pr.ProjectId
			JOIN dbo.ProjectContacts pc ON pr.ProjectId = pc.ProjectId
			JOIN dbo.Users u ON pc.ContactId = u.UserId
	WHERE   (
				pc.ContactId = 4784
				OR sr.InsideSalesRepId = 4784
			)
			AND sro.servicerequestid = sr.servicerequestid
);

#5

Yep that gives me the individual service request Ids with no duplicates, but I want the entire results set from the dbo.ServiceRequests sr table.


#6

Add the additional columns you want. If you want all the columns,

SELECT 
	sro.*
FROM
	dbo.ServiceRequests sro
WHERE EXISTS
.....

#7

Oh snap. You're my hero.
Thanks!


#8

Now I just gotta convert it back to Linq...ha.