MySQL Database Tutorial - 21 - Another Subquery Example Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

all righty guys welcome to your 21st -
quelle tutorial and in this lesson I'm
going to give you guys another more
confusing example of a sub-query however
once I show you guys this example it
should pound the idea of so queries
through your head so hard that you
couldn't forget it if you wanted to so
in this problem what we're going to be
solving is we want to find out which
seller has the cheapest frogs so of
course we know that if we go over to our
items table there are a bunch of
different sellers with a bunch of
different frogs but we want to find the
best price on them so let's go ahead and
hop over to where we type in our queries
and in order to solve this problem again
let's break it into a couple steps the
first thing we need to do is find out
what sellers are even selling frogs and
then once we have a list of all the
sellers that are selling frogs we need
to figure out the cheapest of those in
the list so again like I said the first
thing we need to do is get a list of the
sellers that are selling frogs so let's
go ahead and select the seller ID which
is basically the seller from the table
items where the name and remember the
name is just the name of the items is
like and we'll just use that wildcard %
boxes of frogs but you have to spell it
correctly that's a key so let's go ahead
and search and see what we get up with
we ended up with 68 6 and 18 and those
are the 3 sellers who are selling frogs
now even though whenever we get our
results in MySQL it presents the results
is a table with the column 68 6 and 18
but not only is this a table but this is
a list as well so therefore you know how
I told you that in some functions like
concat use a list and whenever you use
the keyword in you use a list well you
can also use this entire query right
here and it will treat it as a list as
well so basically whenever we use a list
in MySQL we can just go ahead and plug
in this query instead and therefore a
query
inside another query inception aka
sub-queries but it's just so much funner
to say inception so now let's go ahead
and copy this entire query I already
have it copied so basically we solved
step number one we found out what
sellers are selling frogs
so hopefully you copy that query so the
next thing we need to do is we need to
get the cheapest price of frogs out of
that list so let me go ahead and do that
now so select the name of the item which
will be however many boxes of frogs and
the minimum cost from items where name
and let me see how I can do this where a
name is like boxes of frogs now we need
to include this name because whenever we
select the items how can I explain this
whenever we select all the items from
our list of sellers those sellers may be
selling different items too but what we
want is we only want the items who are
boxes of frogs basically we're only in
it for the frogs sorry to sound greedy
but you know we only are and seller
underscore ID is in now remember I told
you guys whenever you use the keyword in
in MySQL it takes a list now our list
was 68 6 and 18 those were the 3 sellers
so basically we're saying okay give me
the name of the product in the minimum
cost from basically all of the items on
our website but the name has to be like
boxes of frogs so basically they have to
be boxes of frogs and the seller has to
be one of these sellers well we already
know that these sellers are the ones
that are selling boxes of frogs
so that should save our query a little
bit of time now whenever we hit go we
see that ok the cheapest boxes of frogs
that we can buy is three boxes of frogs
for $30 and basically 50 Cent's however
instead of having the type in this list
every single time we need to add a
sub-query in here because you know what
what
our users that are actually using the
website are looking for information like
we just did we're not going to be there
every single second to monitor what
they're doing on the website and
whenever they find a list
we can't type it in manually for them so
in order to do something like this we
need to add a sub query as a query in
here so go ahead and copy that query
that you have before and remember this
is the very first query that you're in
this is what returns 68 6 and 18 so
remember from last time my SQL works
from the inside out
so not only whenever well I want to
mention this in the last tutorial it
figured out one value but it could also
figure out a list of values as well so
again the first thing my SQL is going to
do whenever it runs a query is it's
going to figure out what's in between
the parentheses so it runs this query
first and it gets the result of 68 6 and
18 so basically it runs this entire
query but when it gets to this it's
going to treat this query as literally
the numerical value 68 6 and 18 so
select the name and minimum cost from
items where the name of the product is
like boxes of frogs and the seller ID is
in the list 68 6 and 18 so now let's go
ahead and run this and we should get the
exact same result so how awesome is that
basically you plug in a list how can I
say this take a deep breath basically
instead of typing in a list manually we
just plug in another query and it treats
it as a list simple enough and I want to
mention this I won't show you guys an
example because if I do you're probably
just going to stop watching my tutorials
but we can also run a query within a
query within a query so you know how I
put one query inside another query well
we could also put another query inside
that query and it would be like 3 layers
deep if you ever watch the movie
Inception you know exactly what I'm
talking about by the way I really like
the movie Inception I probably shouldn't
mention that but I won't show you guys
an example
that because if I do your brains are
probably going to explode
and no one really does that anyways
whenever they're doing complex queries
like that like four or five sub queries
deep it's easier just to use join tables
and I'll show you guys that later on
anyways I'm going to do you favorite and
I'm going to shut up right now
and I'm going to be done with sub
queries for now in the next story we're
probably going to learn about joining
tables it's going to be awesome so thank
you guys for watching hopefully you
understand sub queries and I'll see you
in the next lesson