remove duplicates from sql union

Question

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

User · Answer

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

User · Answer

Union will remove duplicates  Union All does not

User · Answer

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

User · Answer

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

User · Answer

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   lt your query gt   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

User · Answer

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

[sql] remove duplicates from sql union

Examples related to sql

Examples related to tsql