4.4 Joining Many Tables Together

So now we're to the point where we have a lot of the data in the right places. Now we wanna be able to pull everything back and start to make sense of it. So you saw how we started to do joins using the join on previously when we were doing the one-to-many relationship. And when you're doing many-to-many relationships, it's actually very much the same. So let's say that I wanted to get back the guests, their first and last name, and I wanna get the dishes that they're bringing back. So let's go ahead and see what that looks like. So I'll do a select, now I'm gonna wanna use aliases and so in order for me to do that I'm gonna kinda need to think about what those aliases are going to be during the select part of the statement. So in this case, I know that I'm gonna be referring to anything with a guest as g, and then anything with a dish as d. And then we'll be referring to the linkage table, the guest dish as gd, so just to kinda think about those things. So when I'm pulling back that data and I wanna display it to the end user, or get those values back, what is it going to look like? So I'm gonna have my guest.first_name, I'm going to have my guest.last_name, and then I wanna get back the dish.name. So those are the pieces of information that I wanna get back, so I wanna know where those pieces of information are coming from. So now I need to say where this is coming from, so I'm going to say from guest g. Now remember, this case I want to do a join. So I wanna join my guest table and I want to join that to my guest dish table. So I'm gonna join it to that linking table and I'm gonna refer to that as gd, as I mentioned before. And we're gonna say on g.ID is gonna be equal to gd.guest_id. So this is kinda similar to what we saw before, but we've only made it halfway. We've only gone from the guest to the guest dish table, we have to finish that linkage into the dish table. So now we're going to join again, and this time we're going to join the guest dish table to the dish table. Dish is d, and we're gonna join that on d.id is equal to guest dish, gd.dish_id. Now, I know there's a lot of things going on there, but you just have to remember that you could have multiple joins together and they go sequentially. So I'm now joining my guest_dish table to my guest table on this relationship here. And then I'm joining the guest_dish table to my dish table on this relationship right here. So if go ahead and hit Enter, you're gonna see now that I've got some good stuff going on. I have now the names and the list of things that these people are bringing. Bu this will work, but now I'm getting multiple records back for each individual person based on their relationship. So since there are multiple entries in the guest dish table for both Derek and John, I'm getting multiple records back for each. So I can see exactly what they're bringing, but what if I don't wanna do it that way? What if I want a single record for all of these things that are coming back in this many-to-many relationship. Well, I can do that with a little bit of help from grouping and we're gonna show you how to do that right now. So let's go ahead and start with the same query that we had before. So I'm gonna cheat a little bit, and I'm gonna arrow up. And so this is how we were starting, we were getting the first name, we're getting the last name, and the name. But in this case, we're gonna wanna do a concatenation. We're gonna wanna take all of these records, and we want to comma separate them in a single record for each of the other people that we're bringing back. So I wanna see Derek Jensen bringing salad, pasta and then I wanna see John Wilson bringing pasta, garlic, bread. So in order to do that I'm gonna take advantage of a little bit of a function here that's built into SQL called group_concat. So what this is going to do, is it's going to allow me to group things together to be able to comma separate them automatically instead of me having to do string concatenation, this is going to actually do it for me. But in order for this to work, we're gonna have to do a little bit more work. So then we'll come down to the next line and I'm once again going to cheat because my linkages are gonna be the same. So I'm gonna say from guest g then I'm going to join that to guest_dish. Then I'm going to join that to dish, so all of that's the same. But I'm gonna remove this semicolon cuz there's one more thing that I have to do. Well, actually, let's go ahead and just let that go. If I try to run this right now, this group_concat is assuming that everything is grouped together, but if I don't I'm actually gonna get an error. So it's saying, in an aggregated query without a group by then I can't do this. So the problem here is that I'm trying to query and do a grouping here with this group_concat, but the results coming back for my query are not grouped. So the way that I do that is I actually introduce, so I'm gonna bring all this back so it's easier for you to read. And as you can see here, now I've removed the semicolon and I have dropped down to the next line. So what I need to do now is, I need to introduce the concept of a group. And so I'm going to use the group by filter in this, where I want to specify what I'm grouping by. Now, how do I determine that? Well, when I'm doing a many-to-many relationship, I can group by something that is common to both of them in my linking table. So in this case, I have a linking table of guest dish, and what's common between them is going to be that first column in there that guest id. So in this case, I'm going to do a group by gd.guest_id. So this is now going to group all the results based on the guest that is being linked between those two tables. So if I were to hit Enter now, I'm gonna get back a single result for each person. So I'm gonna see Derek Jensen is bringing salad and pasta and that John Wilson is bringing pasta and garlic bread. So that is a little bit easier to work with when I'm trying to determine who is bringing what. So there now you have a way to not only join two tables in the one-to-many, but you can now also join many tables together in a many-to-many relationship and start to group the results together to make it easy to read.