Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Join Tables in Postgres with Complex Conditions

    Brett CassetteBrett Cassette

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

    postgresPostgreSQL
    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss