[Loud drinking]
Okay i'm caffeinated
hi! thank you for having me let's get right
into it. Okay, okey dokes so let us start talking
about this case this should be fairly simple fairly
straightforward we have two tables person and address these are two tables
the person table has person id first name
and last name the address table has address id
person id city and state uh what i want you to get out of this is
first name last name city and state and one sql query
but regardless if there is an address for each of these people all right
good luck well this seems very straightforward but let's make sure that
we don't trip and fall by moving too fast it looks
like we have two tables we're gonna have to combine them and the
best way to combine these two tables i'm imagining is with a join but it
depends on what kind of join we're looking for
since you said that we need to get each address regardless of whether
there's a person in the person table one of the first tables
then we need to do a left join which is different from the default
inner join that is taken by default uh if you don't specify what type of
join it is so left join takes everything from the
first table the left table and it doesn't matter if that specific
entry is missing from the right table so let's think about where to join them
well it looks like the person table has person id that's
the primary key and the foreign key here
person id seems to map directly to the person table so the address and
person table have a common uh key and then we can join
on that so what it's going to look like and let's map this out right here
is that we have this table and we have this table
and ultimately we want to have a table that has first name
last name city and state so what it's going to look like here
is we're going to take the first name last name
city
and state
and it's gonna take um the first name let's say abby
last name richards
and that's going to be from the table up here and then it's going to take
the city and stay from the table here so it's going to say
like sf
california right but you but this is a case in which
the address exists um and the purse the address exists for
the person that we're querying for now what if the
dress does not exist so let's say carmen
san diego we don't have an address for her so if
this was an empty entry we still want it to
show up just as either a null or a missing
entry so this is the kind of table that we're looking for at the end
okay
now that we've visualized what we're looking for let's take
a select and then let's uh look at the four columns that we want
to select so now we're gonna start writing code
the first select statement is gonna have first name last name city and state
and then we're gonna take it from the first table person
let's alias that as p so that we have an easier way to understand
which columns are coming from which tables p
left join because we want everything in person table every single person
first name last name it doesn't matter if they exist in the
in the uh address table so left join address
that's daily stats press a on and this is where we take the uh
the primary key of the person table person id
and the foreign key of the address table
okay and now all we need to do is to note that we want
first name from the p table last name from the p table
city from the a table and state from the a table okay so i'm going to
get rid of this visualization here so that we can just run this code
that was very well written very well understood
by me thank you for your time peace