Menu
Given the following tables:
Recipes | id | name | 1 | 'chocolate cream pie' | 2 | 'banana cream pie' | 3 | 'chocolate banana surprise' Ingredients | id | name | 1 | 'banana' | 2 | 'cream' | 3 | 'chocolate' RecipeIngredients | recipe_id | ingredient_id | 1 | 2 | 1 | 3 | 2 | 1 | 2 | 2 | 3 | 1 | 3 | 3 How do I construct a SQL query to find recipes where ingredients.name = 'chocolate' and ingredients.name = 'cream'?
411
An intermediate table is a table created on the database to store temporary data that are used to calculate the final result set. These tables can either be 'permanent' or 'true temporary'. An intermediate table is a table created on the database to store temporary data that are used to calculate the final result set.
Split large queries into smaller components to use with Automation Studio's query activity. This page demonstrates how a single query can be divided into multiple more efficient queries.
Inner Join is the method of retrieval of data from multiple tables based on a required condition and necessary conditions are that there must be common columns or matched columns between the two tables of the database and the data types of columns must be the same.
When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.
Use:
SELECT r.name FROM RECIPES r JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id JOIN INGREDIENTS i ON i.id = ri.ingredient_id AND i.name IN ('chocolate', 'cream') GROUP BY r.name HAVING COUNT(DISTINCT i.name) = 2 The key point here is that the count must equal the number of ingredient names. If it's not a distinct count, there's a risk of false positives due to duplicates.
155
This is called relational division. A variety of techniques are discussed here.
One alternative not yet given is the double NOT EXISTS
SELECT r.id, r.name FROM Recipes r WHERE NOT EXISTS (SELECT * FROM Ingredients i WHERE name IN ('chocolate', 'cream') AND NOT EXISTS (SELECT * FROM RecipeIngredients ri WHERE ri.recipe_id = r.id AND ri.ingredient_id = i.id)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
