In the previous episode, we wrote this query to create a list of all customers, as well as the total number of rentals they currently have checked out. But now, let's take it a step further. I'd also like to include the address of the customer's nearest store, or their home store. Alright, let's see how we can grab that. Well, yes, we have customers, and customers have stores, and stores have addresses, and addresses have their main location there. This is what I want included as part of that information. Okay, so it sounds like we need multiple joins, because we've already done one to pull in information about the rental, and that allows us to create the aggregate here.

Okay, so it sounds like we need multiple joins, because we've already done one to pull in information about the rental, and that allows us to create the aggregate here. Let's do another one. I'm also going to join in the addresses table on the condition that the addresses.id is equal to, and let's think, there is no address_id for the customer. Well, there is, but be careful. This is the customer's home address. It's not the store address. So I don't care about that in this case. It sounds like I need to go into the stores table and find the address_id for the store.

So I don't care about that in this case. It sounds like I need to go into the stores table and find the address_id for the store who is associated with the customer. Gets a little tricky there, but you just have to break it down. Okay, so pull in the addresses table, and the connecting tissue is that the address_id needs to match up with, well, the store's address_id. But think, that's not going to work, and if we give it a run, it'll fail. And that's because we haven't pulled in a stores table. We're working with customers, and then we joined in the rentals, and we joined in the addresses table, but now we're trying to pluck something from the store table, and it's not

We're working with customers, and then we joined in the rentals, and we joined in the addresses table, but now we're trying to pluck something from the stores table, and it's not available. It's not part of the query. So here's where we can reach for a subquery, and as it turns out, you can substitute a subquery any place you reference a field or a table name. So in this case, I'm going to remove this and replace it with a whole new query, a query that fetches the address_id I care about. Select the address_id. I want to fetch one value here from the stores table where the stores.id is equal to the

Select the address_id. I want to fetch one value here from the stores table where the stores.id is equal to the id of the store for the customer, that one. So now, if I give it a run, it works. So I hope that makes sense. We are joining in the addresses table on the condition that the id of the address is equal to whatever is returned from this subquery. And this subquery simply fetches the address_id for the store, but specifically the store that is associated with our current customer. So these two need to match up here.

that is associated with our current customer. So these two need to match up here. All right, but yeah, if we run it, we get the same results as we did before, and that's because we're not selecting anything from that address table. Let's fix that now. Let's pull in the addresses. So I want to pull in this. However, if I give it a run, we get that same issue again. Group by clause contains a non-aggregated column, and it's referencing that new address. So this is exactly what we ran into in that previous episode, but this time, we understand

And this is where you see things like C. So if you took that approach, if you run it now, it's going to fail. And that's because we are referencing customer as C at this point. So we'll update all of these occurrences. And if I give it a run, now that works again. It's entirely up to you. Just be careful. Sometimes when you add all of these aliases, tableOne, tableTwo, and you're just using the first initial, just like in your php or your JavaScript, these short variable names, if you're not careful, they end up being even more confusing.

the first initial, just like in your php or your JavaScript, these short variable names, if you're not careful, they end up being even more confusing. So just be thoughtful about it. Anyhow, this is our final query. But one last thing before I let you go. Notice down here, it took 111 milliseconds for this query to run. And believe it or not, that's not the fastest thing in the world. So an important thing to appreciate is that for any given set of information, you could have written a dozen different queries to achieve it. MySQL is very flexible in that regard.

And in general, subqueries do come with a cost. What if we did this instead? Let's join the stores table on the condition that the store's store_id is equal to the customer's store_id. OK, so now I have access to the information here, which means I can join the addresses table and use this as a reference, like this. Now I'm going to join the addresses table on the condition that the addresses.id is equal to the stores.address_id. So now if we try it again, we still get the same information, but notice that it's nearly twice as fast.