How to join
3 Tables In Sql With Where Clause
Part 10 Join 3 tables in sql server
Sharing buttons:
this is part 10 of sequence of interview
questions and answers video series in
this video we'll discuss how to join
three tables in sequel server joining
three tables or even more it's very
similar to how we join two tables let's
understand this with an example we'll be
using these three tables in this demo
departments generous and employees
notice that with an employee's table
Department ID is a 4nk referencing
department ID in departments table
similarly general ID is also a 4nk
referencing gender ID in genders table
now based on these three tables we want
to write a query which is going to
produce the output that you can see here
we want to retrieve employee name
department name and gender of every
employee employee name comes from
employees table Department name comes
from departments table and general comes
from generous table so if we have to
produce this output then we will have to
join all these three tables let's see
how to achieve that the first step here
is to create these three tables which I
have already done and here is the sequel
script that can do it now let's write
the query so we want to select some
columns will populate select list in
just a bit from employees and we are
going to join employees with departments
table so here we need to specify the
join condition so how are we going to
join employees and departments what is
the common column between these two
tables department ID so we are going to
use that column in both the tables to
join these two tables so employees dot
department ID e Clause departments dot
department ID in the Select list we can
now specify employee name and Department
name so when we execute this query what
is that we get every employee name and
the department which they belong now we
also need the gender of an employee so
to get the gender we need to join gender
is table so we use another join keyword
and then specify the name of the table
genders now what is the common column to
join generous table
if you look at employees and gender
stable the common column is gender ID so
you're going to use that column to join
these two tables now so on genders dot
gender ID equals employees dot gender ID
now we can retrieve gender from genders
table so when we execute this query
notice that we get employee name
Department name gender and look at the
query here we are joining all the three
tables now if you want to join another
table you simply use another join
keyword specify the name of the table
and join condition all right now let's
look at another example
now let's say we want to display total
number of employees grouped by
Department and then by gender so within
every department I want to find out how
many male and female employees are there
so if you look at IT department here we
don't have any female employees and
total number of male employees is two
similarly in payroll department we don't
have any male employees and the total
number of female employees is two but
with an HR department we have both male
and female employees that is the total
of male employees is one and female
employees is one so we want to produce
this output let's see how to achieve
that so here we are retrieving the
employee name but now we don't want
employee name we want the department
name and gender and we want the total
number of employees by Department and by
gender so that means we will have to use
group by so what do we want to group by
we want to first group by department
name and then group by gender and to
retrieve the total number of employees
we use the count aggregate function so
count of star let's say the column name
is total employees so now when we really
execute this query what is that you know
the results are not sorted to sort the
results let's use auto by we want to
first sort
department name and then by gender name
and notice that we are still joining the
three tables so when we execute this
query notice that now you know we get
the output that we have seen on the
slide that's it for today thank you for
listening have a great day
