asked on

Distinct values from two tables

How can I select distinct values from an column from two different tables.
Table1 Table2
Code ClientCode
A a
B D
C f

Query result : A,B,C,D,f

Vitor Montalvão

I'm not sure if I understood your question but try this:

SELECT Code
FROM Table1
UNION
SELECT ClientCode
FROM Table2

Open in new window

RIAS

ASKER

Vitor,
Thanks but, got this error:

Msg 468, Level 16, State 9, Line 5
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.

Rgonzo1971

Hi,

pls try

Select Distinct Code from (SELECT Code
FROM Table1
UNION ALL
SELECT ClientCode
FROM Table2) as T;

Open in new window

Regards

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Oh, avoid to use DISTINCT since that's a performance killer keyword.

RIAS

ASKER

Thanks for the solution as always!

RIAS

ASKER

Vitor,
So is there a alternative for

Select Distinct col1 from table1 ?

Vitor Montalvão

Sure it has:

SELECT Col1
FROM Table1
GROUP BY Col1

Open in new window

Anjala Baby

Hope this works:

select distinct code
from (
    select code from table1
    union
    select code from table2
) a

Open in new window

RIAS

ASKER

Thanks Vitor!

Vitor Montalvão

@Anjala: You don't need the distinct keyword if you're using UNION. UNION will filter out the duplicates by default.

RIAS

ASKER

Vitor,
Any suggestion on

ALTER PROCEDURE  [dbo].[TRL_Select_DistinctColumn]
(
     @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @ColumnName2 varchar(100) = null,
	 @ColumnName3 varchar(100) = null,
	 @ColumnName4 varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	SET @SQL = 'SELECT DISTINCT ' 

	
	
			   
       IF @ColumnName1  <> '' AND @ColumnName2  <> '' AND @ColumnName3  <> ''  AND @ColumnName4  <> ''   
		  SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+  ',' + ' [' + @ColumnName3 + '] '+  ',' + ' [' + @ColumnName4 + '] '+'   FROM  [dbo].[' + @tableName + '] '
	   
	   ELSE IF     @ColumnName1  <> '' AND @ColumnName2  <> ''  AND @ColumnName3  <> ''  
		  SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+ ',' + ' [' + @ColumnName3 + '] '+ '     FROM  [dbo].[' + @tableName + '] ' 	   
	   
	   ELSE IF     @ColumnName1  <> '' AND @ColumnName2  <> '' 
		  SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+ ' FROM  [dbo].[' + @tableName + '] ' 	  
	   ELSE IF  @ColumnName2  = ''
			   SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'   + ' FROM  [dbo].[' + @tableName + '] ' 
	  
	
	  SET @SQL = @SQL + ' WHERE ( DeleteRecord is null or DeleteRecord = 0)  '
     		
	  

       EXEC(@SQL)



		
   
	
END

Open in new window

Vitor Montalvão

RIAS, try this solution to get rid of the DISTINCT keyword:

ALTER PROCEDURE  [dbo].[TRL_Select_DistinctColumn]
(
     @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @ColumnName2 varchar(100) = null,
	 @ColumnName3 varchar(100) = null,
	 @ColumnName4 varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	declare @SQL varchar(500) = null
	declare @GroupBy varchar(500) = null
	-- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	SET @SQL = 'SELECT ' 

	IF @ColumnName1  <> '' AND @ColumnName2  <> '' AND @ColumnName3  <> ''  AND @ColumnName4  <> ''   
		BEGIN
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+  ',' + ' [' + @ColumnName3 + '] '+  ',' + ' [' + @ColumnName4 + '] '+'   FROM  [dbo].[' + @tableName + '] '
			SET @GroupBy = 'GROUP BY [' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+  ',' + ' [' + @ColumnName3 + '] '+  ',' + ' [' + @ColumnName4 + ']'
		END
	ELSE IF  @ColumnName1  <> '' AND @ColumnName2  <> ''  AND @ColumnName3  <> ''  
		BEGIN
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+ ',' + ' [' + @ColumnName3 + '] '+ '     FROM  [dbo].[' + @tableName + '] ' 	   	   
			SET @GroupBy = 'GROUP BY [' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+  ',' + ' [' + @ColumnName3 + ']'
		END
	ELSE IF  @ColumnName1  <> '' AND @ColumnName2  <> '' 
		BEGIN
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + '] '+ ' FROM  [dbo].[' + @tableName + '] ' 	  
			SET @GroupBy = 'GROUP BY [' +  @ColumnName1 + ']' + ',' + ' [' + @ColumnName2 + ']'
		END
	ELSE IF  @ColumnName2  = ''
		BEGIN
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'   + ' FROM  [dbo].[' + @tableName + '] ' 
			SET @GroupBy = 'GROUP BY [' +  @ColumnName1 + ']'
		END
	  
	SET @SQL = @SQL + ' WHERE ( DeleteRecord is null or DeleteRecord = 0)  ' + ISNULL(@GroupBy,'')
     		
	  
	EXEC(@SQL)
END

Open in new window

RIAS

ASKER

Can't thank you enough Sir!