Get Distinct Unique fields from Multiple SQL tables

Today in this article, we will see how to get distinct unique fields from Multiple SQL tables.

We will write a query to get a unique employee id from a table and then from a collection of tables.

We will cover below scenarios,

Get distinct unique fields from a Single table
Get distinct unique fields from Multiple SQL tables
Get distinct unique fields with different field names

Get distinct unique fields from a Single table

Let’s get distinct unique fields from a single SQL table later we will extend it for multiple SQL tables

Command

select distinct <fieldname> from  <table> with (nolock)

Example

select distinct employeeId from 
Employee with (nolock)

Get distinct unique fields from Multiple SQL tables

Let’s now get distinct unique fields from multiple SQL tables

Command

 select distinct <fieldname> from  <table1> with (nolock)  
 union
 select distinct <fieldname> from  <table2> with (nolock)  
 union
 select distinct <fieldname> from  <table3> with (nolock)  
 union
 select distinct <fieldname> from  <table4> with (nolock)

Example

select distinct employeeId from Employee with (nolock)

union

select distinct  employeeId from FinanceRecord with (nolock)

union

select distinct  employeeId  from PersonalRecord with (nolock)

Please note that UNION concatenates result sets from two queries and does not create individual rows from columns gathered from two tables and also helps excludes duplicates.

Get distinct unique fields with different field names

It could be possible that fields or column names from a single SQL table are not matching with other SQL tables but values are matching.

In such case later we will extend the query as below to match the multiple SQL tables

Command

select distinct <fieldname> from  <table1> with (nolock)  
union
select distinct <newfield1> as <fieldname> from  <table2> with (nolock)  
union
select distinct <newfield2> as <fieldname> from  <table2> with (nolock) 
union
select distinct <fieldname> from  <table4> with (nolock)

Example

select distinct employeeId from 
Employee with (nolock) where updatedDate > (getDate())

union

select distinct  employeeId 
from FinanceRecord with (nolock) where updatedDate > (getDate()) 

union 

select distinct empUnique as employeeId  
from PersonalRecord with (nolock) where updatedDate > (getDate())

References :

Configure DbContext in Entity framework ASP.NET Core – Best Practices

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.