SQL — insert values with joined IDs from another table

You’ve got values for which you have IDs in a table. Insert the IDs in one go!

What do you do when our table expects id’s but all we have are strings? Let’s imagine we’re a restaurant. We store every menu item and its ingredients into our database so that we can keep track of all kinds of statistics; how often a dish is ordered and which ingredients are most popular e.g.

We want to store a new recipe into the recipes table but there’s a problem: this table expects an ingredient_id and not the ingredient_name that we currently have. How can we insert these IDs into the recipes table while we only have the names?

This article focuses on storing those recipes and ingredients in the smartest way possible by using a unique ingredients table. After reading this article you’ll:

• understand the unique table
• understand how to use a THROUGH table
• be able to insert only unique values into the unique table
• be able to insert into a table while retrieving id’s from the unique table

First, we’ll set up some tables and then we’ll get into the query.

Setup

In this section, we’ll define our database structure and create all of our tables. Then we’ll insert some test data in it. After this we’ll get into how to insert with joined id’s. Notice that this article uses Postgres but the same techniques applies to all relational databases

Database structure

We’ll define 3 tables: one that holds ingredients, one that holds recipes, and a third table that connects the two together:

The ingredients table

First we’ll create the ingredients table and insert some data. Check out this article for a query that can only insert ingredients that are not already present in the table, making sure we never get errors. For this example we’ll use the “normal” insert below:

So our table looks like this:

The recipes table

This table will store recipes that have a unique id and a name:

The recipe-ingredients table

This table connects one or more recipes to one or more ingredients. This type of table is known as ‘through table’; it serves as an intermediary between two tables that have a many-to-many relationship. A recipe can have multiple ingredients and an ingredient can be a part of multiple recipes.

We’ll connect them together with the recipe_id and the ingredient_id:

T

All of our tables are now created, let's start inserting!

Inserting into our recipe-ingredients table

Let’s get to the problem at hand. We want to create some records that connect a record in the recipe-table with some records in the ingredients-table. We need the recipe_id and some ingredient_id’s for this. The problem is that we don’t have the ingredient_id’s, only the ingredient names. How can we insert into the recipe_ingredients-table? For now we assume we know the recipe_id; the value 1. The query below solves this problem:

Let’s take a look and go through the query.

• Line 1 through 8; here we define the dataset that we want to insert. We know the name of the ingredient and the amount.
• On line 9 we say that we want to insert recipe_id, ingredient_id and the amount into the recipe_ingredients table. This is our goal
• In the SELECT data from our input values (line 1 through 8) that we define as d. We join this dataset on the ingredients table, matching ingredient names.
• This is where the magic happens: as you can see we select the value 1 for the recipe_id, the id from the joined ingredients table and the amount from the input-dataset.

Taking recipe_id into account

The trick is to define our input-data as a dataset, join that with the required tables and then only insert the ids. Pretty easy right? Let’s try this with a more difficult example where we don’t just join the ingredient_id but also the recipe_id.

We perform the trick from the previous query twice: we join the recipe from the inputvalues on the recipe column in the recipes table. Then we extract the recipe id from that table and insert it into the recipe_ingredients. Check out the result below:

Conclusion

In this article we’ve gone through a few pretty neat techniques: a UNIQUE table, a THROUGH table and inserting joined values. I hope to have shed some light on them. If you have suggestions/clarifications please comment so I can improve this article. In the meantime, check out my other articles on all kinds of programming-related topics like these:

• DELETE INTO another table
• UPDATE INTO another table
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Save upserting
• Inserting into a UNIQUE table

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!