Solved

Distinct values from two tables

Posted on 2016-11-14
14
39 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 50

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 51

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 50

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 50

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 50

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 50

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 50

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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