CODEWITHSUNDEEP
sqlt-sql
thegunner
thegunner

Reputation: 7163

remove duplicates from sql union

I'm doing some basic sql on a few tables I have, using a union(rightly or wrongly)

but I need remove the duplicates. Any ideas?

select * from calls
left join users a on calls.assigned_to= a.user_id
where a.dept = 4 
union
select * from calls
left join users r on calls.requestor_id= r.user_id
where r.dept = 4

Upvotes: 103

Views: 196249

Answers (6)

Joe Gurria Celimendiz
Joe Gurria Celimendiz

Reputation: 480

If you are using T-SQL you could use a temporary table in a stored procedure and update or insert the records of your query accordingly.

Upvotes: 0

Alberto Martinez
Alberto Martinez

Reputation: 2670

Since you are still getting duplicate using only UNION I would check that:

  • That they are exact duplicates. I mean, if you make a

    SELECT DISTINCT * FROM (<your query>) AS subquery

    you do get fewer files?

  • That you don't have already the duplicates in the first part of the query (maybe generated by the left join). As I understand it UNION it will not add to the result set rows that are already on it, but it won't remove duplicates already present in the first data set.

Upvotes: 4

Justin Rassier
Justin Rassier

Reputation: 908

If you are using T-SQL then it appears from previous posts that UNION removes duplicates. But if you are not, you could use distinct. This doesn't quite feel right to me either but it could get you the result you are looking for

SELECT DISTINCT *
FROM
(
select * from calls
left join users a on calls.assigned_to= a.user_id
where a.dept = 4 
union
select * from calls
left join users r on calls.requestor_id= r.user_id
where r.dept = 4
)a

Upvotes: 2

Jerry Coffin
Jerry Coffin

Reputation: 490168

Others have already answered your direct question, but perhaps you could simplify the query to eliminate the question (or have I missed something, and a query like the following will really produce substantially different results?):

select * 
    from calls c join users u
        on c.assigned_to = u.user_id 
        or c.requestor_id = u.user_id
    where u.dept = 4

Upvotes: 4

Jeremy Elbourn
Jeremy Elbourn

Reputation: 2690

Using UNION automatically removes duplicate rows unless you specify UNION ALL: http://msdn.microsoft.com/en-us/library/ms180026(SQL.90).aspx

Upvotes: 6

Randy Minder
Randy Minder

Reputation: 48432

Union will remove duplicates. Union All does not.

Upvotes: 234

Related Questions