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

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.

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

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.

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

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