Solved

Distinct values from two tables

Posted on 2016-11-14
14
18 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 45

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 48

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 45

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 45

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

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 45

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 45

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

22 Experts available now in Live!

Get 1:1 Help Now