Hey guys, in this video we will be looking at how we go about querying from multiple tables that are related. Oh as by way of quick recap, we just entered some records into our enrollments and our classes tables. And these both these tables are related to at least one or two of the base tables, which are students, lectures and courses. So classes is related to lectures and courses depicted by the names that we gave our foreign key columns, all right, and then enrollments is related to classes as well as students. Alright, so now we will start looking at how we can look at the details of the classes. So looking at a table here, just just that, that it's kind of difficult to decipher who will lecture one who lecture three is and which course is they are teaching because we may not be in a position to memorize these IDs.

So we want to look at how exactly we will go about bringing about the details associated with each referenced record. So stay in this script file, which already has the part the parts of the query that's saying Select star from school or DB classes, school DB classes, sorry. And so we'll just expand on this. And we're going to start looking at a new keyword or a new concept in databases, which is called joins, because essentially, we need to join on the related records from the respective tables to this table. So the base of the queries classes, and we're going to be joining on the lecture details and joining on the courses details. All right.

So we can just go to the next line and remember you always immediately Hold on, I'll just remove it for now. So it doesn't get in the way. But we go to the next line. And then we introduce a new keyword, which is inner join. Now, let me say that you have different types of joins. And we'll get into the other types of joins later on.

But you also have different ways to accomplish this kind of join. So I'm going to teach you a very standard way which you can actually apply to almost any other relational database engine, because the reality is that each one has its own, you know, quirks in its syntax. There's certain things that my SQL does that Microsoft SQL is a different way that PostgreSQL does another way. So what then this inner join technique is fairly standard across all the platforms. Okay, so following or inner join. I'm going to go very slowly here so that we can understand exactly what's going on.

He states the table that we want to join. So we have one table which I The information we want, but then we need the details. So what table contains the details that we see. So the first table that we would want details from would probably be our lecturers table, because we want to see who this lecture is. So I can just say inner join. lecturers reference the table by name.

And of course, we're not using a using statement up top. So if I use a table, just like that, I have to put on school D underscore dv dot and you see the intelligence is actually helping us there. Any hole, we say INNER JOIN, and then we stay at our table. And then we see on so that's another key word. So INNER JOIN table name on and then we state the condition that the two tables must be joined on. I like to say what do the two tables have in common, what two values do These two tables have in common that they should be joined on.

So simply put, it would be the primary key, sorry, the primary key value of the joining table onto the foreign key value of the next table. All right, so in this case, the foreign key, which is lecture, Id must have a matching value with the primary key column in our lectures table. So these values are what they have in common. And so these columns are what they have in common. So what I'm going to do is say inner join this table on the condition that the ID from classes and I'm going to have to spell this out a bit. And you can see how tedious he can get when you're not using you're using statements, especially when more tables are getting involved, have to be seen database dot table name and then dot again, to get to That ID.

All right, well, then that's not it, it's classy some inner joining on lecture ID being equal to, and then I'm going to have to do the same again, school DB dot, lecturers dot, and then it would be the ID. So once again, I'm joining these two tables on the condition that the lecture ID value that is present in the classes table matches is equal to the primary key ID value in the lecturers table. Now, please know we have six classes listed here. And when I execute this query, you're going to notice that we will only have three and I want to do it and then explain why. So we're down to three. And you see, because I'm selecting star, it's selecting all columns from our classes table.

It's selecting all columns from any table that is being joined on. And so we have all columns. that were there before. And now we have more columns coming back from our lectures table. And if you look at the values that are in the lecture ID in from the classes set up columns versus the values in the ID column coming back from the lectures table, you'll notice that they're the same. And so that would kind of inform why we have three fewer rules coming about than we had before.

And that's because remember that we had no in those lecture ID columns. And I'll just quickly re execute that. Oh, apologies. Let me just read type query. All right, there we go. So we have the results.

That's like Star from classes. And notice we had six three off which had no lecture ID. And so because our condition for joining the two tables, states that both values must be the same, they must be equal. There is no lecturer with an ID as no in our lecturer stable So those three would have been almost emitted from our inner join operation. Alright, and so when we go back to our results, we only have those three with the names and the details off each lecture. So now I have the details on the ledgers, but I'm still missing the details on the course.

So I'm going to have to repeat this line. All right, and I'm going to have the inner join once again. And if you just go to the line and press control, well hold on Ctrl and press D, it actually duplicates that line for you. So that's what I just did. And then we can just go ahead and modify. But then you see that as we start, you know, joining, joining and joining, the more tables that gets into this query is the more I would say messy it looks.

So SQL actually gives us the ability and the opportunity to use what we call aliases. And we would have gone through that when we're looking at simple select queries, but we would have looked at them relative to the columns, you can ask Use aliases on your table names also. So I could call classes instead of writing school DB classes every time I can just see see, all right, and then instead of writing school.db dot lectures, school DB dot lecturers, every time I can just say l or let me let me do it a bit better, so I can see class, and I can see elect. Alright, and then instead of writing all of this, once again, I can just replace all of this part that references the table and just see class. Dogs dots, lecture ID, and I can just see lect.id. So now instead of having to write out, especially since I'm not once again using the using statement, I can replace all of this with just a reference to an alias and then use this in this in the referencing elsewhere.

So I'm going to rewrite this last INNER JOIN statement for our classes using this technique. So I'm going to inner join our inner joining classes with to see Oh, sorry, I'm going to joining courses. So courses, and I'm going to use my ID. So I'm just going to use this you can use, of course, a more descriptive expression in your aliases or it could be very abstract, but of course, you can't have C here and see here that would cause a conflict right so just be very Just be careful with that. So we have courses core and then I'm seeing that I want to enter join class dot courses ID and see the intelligence is helping me along because intelligence knows that class represents classes and inner joining that on core dots, and I believe this would be course ID. All right.

Is it course it is Images double check. Yes, course I There we go. All right. And so and once again, we're still selecting star. So when I execute this, it will bring back Oh, so when you have multiple queries, it will actually give you this result pane with more than one. So results seven is the first one.

And we see here that we're getting back all of our class details, we're getting back all of our lecture details, and we're getting back all of the course details. Alright, so this can come in handy when you are charged with furnishing a report. Maybe your instruction was select all the scheduled classes that have a lecture. And so the inner join is a nice way to just eliminate any null values that may appear. So in this case, we know that lectures can be no if a lecture is no then there's no lecture assigned. So we can just use the inner join, which does does an inherent elimination of that that missing value.

So no, you won't To start tweaking our results set a bit because we have the details that we need. So we really don't need to be seeing lecture ID courses ID, the primary key columns and so on. So I'm just going to tweak the select statement, and I'm going to start selecting the columns that I know are absolutely necessary. So I would want maybe the lecturers name. And then we see we have last underscore name first underscore name for the lectures, but we did look at it earlier. And we saw that we could use concat, which would allow us to see the left dots last underscore name, and I'm going to concatenate that onto a space and bring back and I'll just put a comma there since its last name, first name.

So left dot first name Then I'm going to give this the DNS name. And remember we use our apostrophe, lecturer, full name. All right. So we bring back the lecturers full name, we probably don't need the degree in this in this report. So we just want the lecture. And we want the title of the course.

So I could see core dot title. So I will be notice what's going on here. Because I gave these tables a ss, wherever they were referenced, I can no use those it is is to make sure that I'm getting the cards column from the correct table because if we had students being joined into the square will also have last name and first name, then it would be ambiguous if we only said last name and first name. So by seeing elect dot last name, or if I had students here and I said call that one student, by seeing students or sorry, it's still that last name, we know exactly which table the values should be coming from. Alright, so core, the title, and I'm going to rename this one as course title. And we probably don't need the course core, but we could use a number of credits.

So it's core dotnet core scored. And of course, if it's the only column of its kind, like core score is only column with that name, so it wouldn't be any harm nots referencing it by the eighth. So what I like to do that because if I look back at the query, I can tell exactly where what is coming from. So I just stick to that evil that I know. And I say course code. Alright, sorry, we were said credits, we said number of credits, actually.

So it's number of credits, and that is as number of credits. Alright, and so we can execute this query one Again, and then we'd see that the details sorry, though the query is being run, the details that were once there are no eliminated as well as the obstructs foreign key values, they're all eliminated. And that while they're not going back in the query, they're still playing a vital role in the background pulling the strings are loading the joints. And then once all of the data is, is present and available to us, we're simply selecting the columns that we're absolutely interested in. Alright, so I'm going to share this and I'm going to challenge you. And I'm going to leave this here I'll upload this with the with the quote, of course, but I'm going to challenge you to write your own inner join queries for enrollments to bring back all the details on the course the time So remember, class ID is referencing classes and classes has references to these guys.

So I would want To to bring back the students and the name of the course who is teaching this course and the time of day that this course our class or other will be so always teaching this class or classes or courses it and what time is it and who is the students. So that's my challenge to you. So happy hunting.