join

4-Tables JOIN - SQL Server 2012



Sharing buttons:

sequel server 2012 Pro mrs. Kellman Tov

this is my new book it is available at

amazon.com and in your bookstore let's

consider the following four table inner

join

so first table is employees when

employee territories when territories

and region if we look at one naming

employee territory suggest a junction

table between employees and territories

a junction table represents many to many

relationships so this relationship is

one employee belongs to many territories

and one territory has many employees of

course many can be as few as one rejoin

itself is an equi-join we are joining on

Keys foreign key and primary key we have

a very close predicate it's were in

operator which takes asset on the right

hand side and takes a single value on

the left hand side we are ordering by

two column aliases for territory alias

and were region alias the full name is

coming from the concatenation of first

namespace and last name concat operator

is function rubber is new to sequel

server 2012 let's execute the query

and this is the result

49 rows we can scroll down

let's check out a diagram for way for

table join so we highlight to a query

click on query drop-down menu and select

design query in editor so here is our

diagram let's rearrange it so we can see

it better we have to experiment to get

with right so so we see on this diagram

that region ID primary key is joined

with region ID foreign key

now we our table is regions we can see

it earliest west we our table and T's

forward territories and it is we

employee territories and is for

employees if we are guessing it and we

can see that when employee IDs primary

came by employees table and foreign key

in the employee territory table which we

correctly guessed that it's a junction

table connecting with employees and the

territories table and receive a primary

key in with table is a composite primary

key of territory ID and employee ID

let's cancer from graphical query

designer so what we see when we have to

combine data from different tables we

have to join the tables and the most

logical join is of course joining

foreign key and primary key but as we

see we've all examples we can join

different ways even we're not actually

keys involved but some different data in

summary joining tables is very very

basic to database programming so it's

important that we master the joins this

is the end of a short demo