How to fetch data from multiple table in mysql
Using SQL to Select Records from Multiple Tables
Timecodes:
hello we are going to continue looking
at sql now actually continuing with sql
select a particular query with an sql
looking at how you apply it to
two tables and actually this idea
extends across more than two tables but
i'm only going to show two tables in
this video with two examples so just a
quick recollection because it is
important for this
concept there are two really important
terms in database theory
you've got primary keys and you've got
foreign keys both are fields both for
columns in tables but we are a little
bit different a primary key uniquely
identifies a record
it must be unique for every row in your
table
often something like id but not always a
foreign key
is in another table
and actually is a primary key of another
table so a foreign key is not unique
but in another table is unique and
that's how tables connect in a
relational database
and if you want to therefore use sql
select to get data from two related
tables you need to match up the foreign
key to a primary key
using the where clause
in sql select
so that's really what i'm going to show
you how you use where to join up the
tables there is another operation called
in a join in sql which i'm not going to
teach because personally i think select
is simpler and
reasonable for clauses same idea as last
video just now is the extension of a
second table
if you'll be confused about primary keys
foreign keys please go back to my
database video that'll be linked in the
description as well as a previous sql
video
so let's look at a example i've got two
tables here one you might have seen
before which is albums i've also got a
table called songs
and they're connected
via a foreign key
so look at it here i wonder if you can
decide what is our primary key and what
is our foreign key there should be two
primary keys and one foreign key
well in albums our primary key is id in
fact all of the fields here are unique
but if this was a proper database say
for spotify or apple music you would not
have
no repeats in those fields so id is most
sensible and in songs you might be drawn
to listing because
often the far left field is your primary
key but not always actually here the
most sensible one is ref maybe short for
reference that could be hexadecimal say
for every song and it's really the song
id not the album id
but look we've also got a field called
id that is our foreign key that connects
up the two tables so really you're
looking for
what field in one table is in another
table the names aren't always the same
if they're different you'll get told one
is a foreign key
anyway for this example i want to find
out what are the song names stored how
many streams did they have and what
album are they in so looking for free
fields basically the name
stream count and
album title is what i'm looking for here
so i'm looking for data across two
tables and so therefore i've gotta be a
little bit cleverer about how i use
select
and what i would do is something like
this starting with my first
clause which is quite simple just
selecting the fields which like i say i
name stream count and title
from now i've got a list two tables so i
add a comma like i would in my fields my
two tables are called albums and songs
but for work laws is where things get a
little bit different
i've got where albums dot id
equals songs dot id
now
in terms of this notation with a dot
this is where you've got two fields with
the same name in two different tables if
i just said where id it would know which
id i'm referring to so you've got to put
the table name then a dot then the field
name if you've got duplicate names
across tables
so if you see a dot that just is helping
you know what table to look at
so the id and albums equals the id in
songs is what we're matching now this
would return this table
what it will give us
is the names of our songs in songs
the stream count of those songs but also
the title of the album it belongs to
now here for example we've got two songs
from adele's album 25
and so it matches both of those in our
resulting
table
likewise there are two songs from taylor
swift's album and so we get those in the
table it's a good example of where
we have efficiency created through a
relational database but anyway crucially
we've got a couple of albums not
mentioned in this table we haven't got
mumford and sons we haven't got john
baptiste because they're not listed in
songs for whatever reason their id
doesn't show up in this table and so
therefore it doesn't show up in our
query result
now what it's doing is it's working its
way through
the songs table and looking up this id
it's matching
the id in albums to the id in songs
starting with the suburbs
it goes to id bing four
well songs.id is four so it goes through
the albums table until it finds
four
and so therefore it can connect the song
called the suburbs with the album called
the suburbs
it does the same with our other songs as
well
now i think that's confusing initially i
think what can help is seeing what would
happen if you didn't do that
so let me show you what would happen if
you didn't include the where clause if
you just had the select and from bit
well you'd get a bit of a nightmare
table like this
you can't see all of it because it's so
big
what is happening here is
like i mentioned it's going through
each record in songs initially because
i've got name as my first field listed
and to be honest name and stream count
work fine you know the suburbs has got
123 million streams
but what's not working fine is the title
field because it doesn't know
what
song belongs to what album without that
information about the foreign key it
can't connect up so you can see folklore
is our first album in albums then we are
then 25. it's working its way through
and just matching any record to our song
which is not what i want i'm getting
duplicate data and it is incorrect
so that simple
where albums.id equals songs.id is
important
let's look at another example with a
tiny bit more complexity
and you know you can just learn you've
got to do this but obviously rather you
understand it
so going back to
not going back to including examples
i've shown before
we've got teachers and we've got classes
as two separate tables primary key in
teachers is teacher code and
name is our primary key in classes
teacher code is our foreign key in class
it's not always at the end i just happen
to have it there in my examples
and if my query is or my question is who
is the teacher and what room are 12b itn
what i'm looking for
is the name of the teacher i'm looking
for the room of the class but i'm
filtering based on 12b it
so my
sql query will look something like this
select teachers.name comma room first of
all i'm looking for the name of the
teacher then i'm looking for the room
now room is quite straightforward in my
query but i've got teachers.name
again this is when you've got a
duplicate field across multiple tables
the name in teachers is different to the
name in classes
and so i've got to specify which one i
mean
that's why i'm going teachers.name and
not just name otherwise i wouldn't know
whereas room is on its own i haven't got
any duplicate rooms i've only got one
room filled and one table so i can leave
that without that prefix
from me straightforward just my two
table names teachers and classes
and again i've got my where clause where
i have my
primary key equaling my foreign key
so teachers.teacher code is for my
teacher table and classes.teacher code
is from my classes table i'm matching up
the two so we only get the correct data
there's difference this time is i've got
another part to my where clause i've got
and
so boolean and both sides have got to be
true so i'm also looking for
classes.name to be 12bit i'm only
interested when it is 12b it
so you've got to have
that part of your where clause to do
further filtering after it simply add
boolean and and you can have a few
boolean ands if you need to
now here we've only got one class called
12 bit because it's a primary key and we
get mr brown correctly and f13 correctly
again if i
omitted
that really important bit
teachers.teacher code equals
classes.teacher code it's not going to
be able to match up our records properly
so make sure when you are searching
across more than one table you match the
foreign key to the primary key and the
order isn't important you can switch
fees around and do the foreign key first
then the primary key that's not super
important
now if i did leave this off again it
wouldn't give me the correct result if i
only filtered based on 12bit it can't
narrow down the search in the teachers
table i get all four teachers
with the correct room doesn't make sense
because it can't match up the two tables
so that is important so if you are
taking anything away please take away
the fact that you need to have
your first table dot the primary key
equals the second table dot the foreign
key and generally those two names will
be the same
that enables you to connect up the two
tables
Related queries:
how to retrieve data from multiple table in mysql
how to fetch data from two tables in mysql using php
how to select data from multiple tables in mysql
how to get data from multiple tables in mysql
how to fetch data from two tables in mysql
how to fetch data from two tables in mysql without join
how to retrieve data from multiple tables in mysql using jsp
how to fetch data from two tables in mysql with join
how to select data from two tables in mysql
