Get data from another table SQL
Show
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:
First, we’ll set up some tables and then we’ll get into the query. SetupIn 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 structureWe’ll define 3 tables: one that holds ingredients, one that holds recipes, and a third table that connects the two together: The ingredients tableFirst we’ll create the ingredients table and insert some data. Check out this articlefor 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 tableThis table will store recipes that have a unique id and a name: The recipe-ingredients tableThis 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! Our tables are prepared, let’s start cooking! (image by Maarten van den Heuvel on Pexels)Inserting into our recipe-ingredients tableLet’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.
Taking recipe_id into accountThe 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: How do I reference another table in SQL Server?Use SQL Server Management Studio. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and select Design. ... . From the Table Designer menu, select Relationships. ... . In the Foreign-key Relationships dialog box, select Add. ... . Select the relationship in the Selected Relationship list.. How can I get data from two tables in SQL without joining?You could try something like this: SELECT ... FROM ( SELECT f1,f2,f3 FROM table1 UNION SELECT f1,f2,f3 FROM table2 ) WHERE ...
How can I get data from multiple tables in SQL?SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
|