Solved

Distinct values from two tables

Posted on 2016-11-14
14
27 Views
Last Modified: 2016-11-14
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
0
Comment
Question by:RIAS
14 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41886150
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

0
 

Author Comment

by:RIAS
ID: 41886151
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.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41886155
Hi,

pls try

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

Open in new window

Regards
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41886157
So the columns are from different collation. You'll need to solve this by resolving the collation.
SELECT Code
FROM Table1
UNION
SELECT ClientCode COLLATE SQL_Latin1_General_CP1_CI_AS
FROM Table2

Open in new window

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41886159
Oh, avoid to use DISTINCT since that's a performance killer keyword.
0
 

Author Closing Comment

by:RIAS
ID: 41886161
Thanks for the solution as always!
0
 

Author Comment

by:RIAS
ID: 41886165
Vitor,
So is there a alternative for

Select Distinct col1 from table1 ?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41886168
Sure it has:
SELECT Col1
FROM Table1
GROUP BY Col1

Open in new window

1
 

Expert Comment

by:Anjala Baby
ID: 41886169
Hope this works:

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

Open in new window

0
 

Author Comment

by:RIAS
ID: 41886172
Thanks Vitor!
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41886173
@Anjala: You don't need the distinct keyword if you're using UNION. UNION will filter out the duplicates by default.
0
 

Author Comment

by:RIAS
ID: 41886174
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

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41886178
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

1
 

Author Comment

by:RIAS
ID: 41886179
Can't thank you enough Sir!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now