Using the DISTINCT Statement

[00:00:00]
>> We just did constraints and now we're gonna go do another little project. So one thing before we hopped too far into the project, I wanted to to tell you a little bit about distinct. So let's say we wanted to do SELECT DISTINCT * FROM recipe_photos. Recipes_photos. I don't have that one, maybe recipe_ingredients.

[00:00:44]
And. And you wanna do an ON (recipe_id). Okay. So what DISTINCT does here, look at these two different queries, you can basically ignore the top one. Let me just redo that without the DISTINCT there. What DISTINCT does is, it's gonna find the first incantation of whatever we're looking at, so I said ON (recipe_id) here.

[00:01:25]
So notice that there's only one recipe_id 1. There's only one recipe_id 2. Whereas down here, there's three. And down here, there's two. That's what the DISTINCT part does. Is it basically says, I want the distinct, interesting one of these. Or another really useful one of these would be SELECT DISTNCT.

[00:01:43]
Type. From recipe, no ingredients. I didn't need the ON because I wasn't doing anything window funk. I wasn't looking at other parts of the tables are just looking at just the type, which is what this is. So this tells me all the distinct types that exist in the ingredients table.

[00:02:07]
I only have fruit, meat, vegetable and other. That's it. So that's what the distinct keyword does is it means just give me the all of the individual distinct versions that existed that. All right, whereas if I just do slick type FROM ingredients. I'm gonna get a lot of repetition, all right?

[00:02:28]
So the distinct classes that down into just the distinct various items. So grabbing that and looking at this. Let's say I wanted to just get the first photo of every single item in the database. I can do SELECT DISTINCT on that recipe Id like you saw there above.

[00:02:54]
But this is actually not only going to give me the first photo instead of all photos. So if I do that, notice I only have one of each of the recipe ids. All right, what we did not add back the photos. So let's go ahead and do that really quick.

[00:03:15]
That was in the one to the. The relationships. Yeah, so down here. This one, so you might need to insert this again, I need to. So INSERT INTO recipes_photo. There we go. So now notice for every single one of these recipes, I only have one photo. I have the first cookie, the first empanada, the first jollof rice, the first shakshuka, khachapuri, and then the xiao long bao, cuz I did the left join here, all right?

[00:04:01]
But it doesn't have a photo, which is fine, we still got it, even without the photo. Does that make sense, what that query is doing? It's gonna be necessary because I want you to grab the first primary photo for each one of these. And I can actually even show you how to make this a little bit even prettier.

[00:04:22]
So let me grab this and put this over here and VS code is a little bit easier to read. So you need the r.title and the rp.url. You probably need other things as well, but I'm just gonna show you these two. This xiao long bao doesn't have a photo.

[00:04:43]
Now in my code I could handle that, all right? I could say if there's no photo do a thing, but I can also have a Postgres do the heavy lifting here for me and I can say COALESCE. If it doesn't exist, then give me default.jpg. As url. Pretty sure that should work.

[00:05:15]
And now you can see, I got cookies I got empanada, jollof rice, khachapuri and notice that even though xiao long bao doesn't have. I've been seeing that so much today that I'm my brain is not connecting all of the. I probably wasn't saying it right in the first place so it doesn't matter.

[00:05:32]
Anyway, soup dumplings now has a default that jpg and that's what this COALESCE does. It basically says check this to see if it's no or no like or something like that. And if it is no, give me this, if it's not then just return that and that's what COALESCE does.

[00:05:56]
Does that make sense? Cool. So that's a nice little trick. So that you don't have to go in, check to see is this nulla cross all of your rows that you're returning. Okay. The ON recipe_id means only one of these is allowed per row, all right? So I only have one cookie, one empanada, one jollof rice.

[00:06:18]
So on and so forth. There's a bunch of ways you can solve this. You technically could solve all of this with one query. I actually ended up doing this with two queries in my completed results because I felt like it was more clear and it made more sense.

[00:06:33]
That is gonna be totally up to you of how you choose to solve this problem. It's fine to do multiple queries in one API request. In fact, I would even say it's common. So use your best judgment. Sometimes writing the biggest weirdest query is not the best way to get there.

[00:06:51]
It's better off done in multiple different queries and faster frequently as well, all right? Cuz if you have complex joins, it means Postgres is doing a lot of work for you. Sometimes it's not what you want. I am gonna have you do one thing with me. So come back here to the project and I want you to click on this big SQL query with a bunch of recipes thing right here.

[00:07:16]
That's gonna download recipes.sql. I've downloaded a few times Clif, I make sure to works. Click on that. This opens in my VS code. This might open in a different thing for you. All I want you to do is I just want you to copy Cmd+A, CTRL+COPY, and then I want you to paste this into recipe your database.

[00:07:39]
It's a long query. So you can see here, I put a lot of stuff in your table because I want you to have a lot of recipes and stuff to draw upon, all right? But if you want to look at this is literally everything that you've done before.

[00:07:55]
I just put a lot of recipes in here. Nothing here should be surprising to you. Yeah, nothing should be surprising. So now if you say SELECT, just so COUNT (*) is how you count how many things are in a table. FROM recipes, we have 56 recipes. FROM ingredients We have 126 ingredients.

[00:08:26]
FROM recipe_ingredients, we have 170 connections, all right? So just so the other container that I provided you that we're gonna use in the second half just runs this query exactly. It's actually literally the same query. So you can also just use that other container if you feel more comfortable with that.

[00:08:46]
It's up to you.