Name: Join Tables in Postgres with Complex Conditions
Uploaded: 2017-04-02T15:49:56-05:00
Description: While our data is sometimes nicely managed with surrogate primary key joins, often times older tables can feature join conditions with complex natural keys.

Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

Find Lonely Postgres Data with Left and Right Join

Detect Duplicates in Postgres with Cartesian Product

Find Related Data with Inner Join in Postgres

See All Rows from Different Postgres Tables with Union All

Join Tables in Postgres with Complex Conditions

Create a History Table in Postgres with Joins

Use Joins to Detect Orphaned Data in Postgres

Understand Joins and Unions in Postgres

While our data is sometimes nicely managed with surrogate primary key joins, often times older tables can feature join conditions with complex natural keys. 

https://app.egghead.io/api/v1/lessons/postgresql-join-tables-in-postgres-with-complex-conditions/subtitles

[00:02] Start to build up a complex join condition with multiple requirements. Let's select from the rentings table, inner join on the rentable movies table. This will be a complex condition right here.

[00:18] We'll start out by saying the rentable movies copy number is equal to the rentings copy number. We can visualize this a little better, let's get some more data here. The movie ID is equal to the rentable movie, the movie ID, and the stores. This is the store ID.

[00:46] Let's also get the guests in here. This will be on the guest ID. This is the rentings type guest ID. This is a pretty wide table here, hard to visualize. Let's start to break this table down to help us visualize it.

[01:06] Always helpful to just get the fields that you need so that you can see a little bit better what you're doing. Let's get whether or not it was returned, and when it is due back.

[01:21] We can start to see already why this join condition isn't complete. It's because the copy number is the only thing we're looking at. Right now, it says that I have rented the same movie in both San Francisco and Philadelphia on the same day, because they're due back on the same day.

[01:38] Also, it looks like I rented "Kill Bill," which might be reasonable. We can already tell that the location is going to matter. It looks like rentable movies.storeID = rentings.storeID. That's part of it, and the copy number.

[01:57] We've got the locations, but I can tell you for sure, because I know that I only rented one of these movies on that day. To figure out which of those it is, we'll also have to look at the movieID is equal to the rentings.movieID.

[02:14] This is the complete join condition. This is the primary key on the rentings table. That's important for us to know in order to be able to do this join, and it actually joins the data across all of these fields where they match in rentings and rentable movies.

Join Tables in Postgres with Complex Conditions

Share this video with your friends

Course

Understand Joins and Unions in Postgres