join

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