Solved

Distinct values from two tables

Posted on 2016-11-14
14
31 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 47

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 47

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 47

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
 
LVL 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 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