Thursday, June 17, 2021
In SQL the WHERE clause is the most common join you will see, it relates one or more tables together. For example you want to get the employeeis territory information in the Northwind database but you there are all in different tables.
As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause:
The query above joins the Employees table to the EmployeesTerritories table matching the two tables by the column EmployeeID, then after we get the records with matching records between those two tables, we and the word "AND" to add additional joins based on our first join. This time we want to match the TerritoryID in the EmployeesTerritories table with the TerritoryID column in the Territories which contains the TerritoryDescription field that we wanted. So with the WHERE clause we were able to work with three tables at once in one query.
You can filter the results even more by looking adding more filtering conditions in the WHERE clause. Let's say you want to get only employees who belongs to the Boston territory, from the first query you know that Boston has a TerritoryID of 02116. So to get the employees who belongs to the Boston territory you would write the query like the one below:
Here are the results
As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause:
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e,Territories t, EmployeeTerritories et WHERE e.EmployeeID = et.EmployeeID AND et.TerritoryID = t.TerritoryID
The query above joins the Employees table to the EmployeesTerritories table matching the two tables by the column EmployeeID, then after we get the records with matching records between those two tables, we and the word "AND" to add additional joins based on our first join. This time we want to match the TerritoryID in the EmployeesTerritories table with the TerritoryID column in the Territories which contains the TerritoryDescription field that we wanted. So with the WHERE clause we were able to work with three tables at once in one query.
You can filter the results even more by looking adding more filtering conditions in the WHERE clause. Let's say you want to get only employees who belongs to the Boston territory, from the first query you know that Boston has a TerritoryID of 02116. So to get the employees who belongs to the Boston territory you would write the query like the one below:
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e,Territories t, EmployeeTerritories et WHERE e.EmployeeID = et.EmployeeID AND et.TerritoryID = t.TerritoryID AND t.TerritoryID = 02116
Here are the results
Subscribe to:
Post Comments (Atom)
Search This Blog
Tags
Web Development
Linux
Javascript
DATA
CentOS
ASPNET
SQL Server
Cloud Computing
ASP.NET Core
ASP.NET MVC
SQL
Virtualization
AWS
Database
ADO.NET
AngularJS
C#
CSS
EC2
Iaas
System Administrator
Azure
Computer Programming
JQuery
Coding
ASP.NET MVC 5
Entity Framework Core
Web Design
Infrastructure
Networking
Visual Studio
Errors
T-SQL
Ubuntu
Stored Procedures
ACME Bank
Bootstrap
Computer Networking
Entity Framework
Load Balancer
MongoDB
NoSQL
Node.js
Oracle
VirtualBox
Container
Docker
Fedora
Java
Source Control
git
ExpressJS
MySQL
NuGet
Blogger
Blogging
Bower.js
Data Science
JSON
JavaEE
Web Api
DBMS
DevOps
HTML5
MVC
SPA
Storage
github
AJAX
Big Data
Design Pattern
Eclipse IDE
Elastic IP
GIMP
Graphics Design
Heroku
Linux Mint
Postman
R
SSL
Security
Visual Studio Code
ASP.NET MVC 4
CLI
Linux Commands
Powershell
Python
Server
Software Development
Subnets
Telerik
VPC
Windows Server 2016
angular-seed
font-awesome
log4net
servlets
tomcat
AWS CloudWatch
Active Directory
Angular
Blockchain
Collections
Compatibility
Cryptocurrency
DIgital Life
DNS
Downloads
Google Blogger
Google Chrome
Google Fonts
Hadoop
IAM
KnockoutJS
LINQ
Linux Performance
Logging
Mobile-First
Open Source
Prototype
R Programming
Responsive
Route 53
S3
SELinux
Software
Unix
View
Web Forms
WildFly
XML
cshtml
githu
thanks for sharing these database topicsdigital service company chennai
ReplyDeleteOnline PDF to Word converter free to convert documents online, Upload your file and transform it into Word in one click. Convert your Doc and Docx right now into Word and PDF format. Visit our Website!
ReplyDelete