Using a Derived Table

Sharing buttons:

in this video we're going to use a

derive table and an outer join to get

the information that we need from the

data so what we want is a list of the

users that have three or fewer

registration including those that have

no registrations so I'm going to start

this by first just getting a count of

registrations and so to do that we do a

select statement and we put one value

that we want to get so we can you get

one column and then we can count it so

we're going to count the user IDs and

we're getting this from registration and

to get a count we have to group by so

we're going to group by that same column

that we got which is the user ID so if

we just run this we get a list of all of

the user IDs and the registrations that

they have the count of how many

registrations they have so what this

does for us is just provides the ones

that have a registration notice there's

none in here that have no registrations

so there's no zero count here and and so

we need to get this idea well how do we

get the ones with no registrations well

really looking for is all the user IDs

so we want every user ID and we want the

ones that have account so we want to do

a join between this table and this table

notice that when you do a select

statement it returns a table our result

here is a table and we can join a result

table with another table that's called a

derived table and the way that we do

that is we just created a select

statement select ok select and we can

get the user ID we could go ahead and

get first name and last name because

that's in that user table we can get

everything from it and we want count as

well now to get count we're gonna have

to give it a name notice how there's no

column name here so

we can say count as and give it a name

there's count so now it will have a name

and we can and we can get that right and

we want to get all of those values from

and here we're gonna do two tables we're

gonna do the user one table join and

we're gonna do this derived table and

when you do a drive table you need to

give it a name as well so it has to have

a name and we'll for the name here we'll

do as and we'll call it user count and

now it's a table that comes from a

result and it has a name and it's giving

us a red line here and that's because

there's a user ID in this table and a

user ID in that user one table so it

doesn't know which one so we'll just go

ahead and say it's the one from the user

one and now let's do that joint and kind

of watch here we've got a hundred and

nine different records that have a

registration so if we execute that oh

we're not finished

just like with any regular join we've

got to do the on Clause right so we how

are these two joined well they're joined

on that user ID so user one dot user ID

equals user count dot user ID and that's

how they're related okay now let's

execute it and there we go now we have a

where all of our listed now we've joined

it so now we have the first and last

name but we still don't have the ones

that have no registration and that still

isn't showing up and that's because

we're doing an inner join and an inner

join is the intersection it's where

these two are the same where they have

values in common but we want to actually

get all the users not just the ones that

are in the registration table so

here's the case where we want to use the

outer join and specifically there are

three types of outer joins there's a

left outer join a right outer join and a

full outer join and it depends which

extra values you want to use key you

want to in the result so here's the left

table here's the right table and so you

can decide do we want everything in

which table well what we're looking for

is all the user IDs in this table so

it's a left outer join now it will take

where they intersect plus everything in

this table so let's execute that and

there now we're picking up all the user

IDs and if they don't have a

registration it's showing up as null

notice this is different than zero so

let's go ahead and do the where cuz we

want these ones that have 6 & 4 & 5 we

don't want we only wanted if it's 3 or

fewer so let's go ahead and add the

where and this is where a count is less

than 4 and run that and here we're back

to just having one twos and threes and

we've lost the nulls and that's because

when null is compared it doesn't count

null is not less than 4 it's not greater

than 4 it's not equal to 4 nulls are not

a number that you can compare so they

have to be treated in their own way so

we say well count less than 4 or count

is null and that's how we access them

access the null value we execute that

and there we have the result we're

looking for there's the list of all the

users that have three or fewer

registrations including none at all