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
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 :
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.
