1

Postgres Database with 3 tables:

recipes(id,recipe_name)
ingredients(id,ingredient_name)
recipes_ingredients(recipe_id,ingredient_id)

Is there any way to write a dynamic sql select that creates a list of the recipes with one column for each ingredient in the ingredients table that has a yes if it is related to the recipe?

Like:

+---------------+-------------------+-------------------+-----+
|  recipe_name  | ingredient_name 1 | ingredient_name 2 |  …  |
+---------------+-------------------+-------------------+-----+
| recipe_name 1 | yes               |                   |     |
| recipe_name 2 |                   | yes               |     |
| recipe_name 3 | yes               |                   | yes |
+---------------+-------------------+-------------------+-----+

Got it to show 0 or 1 ok but is there a simple way to make it dynamic so I don't have to update with every item from the ingredient table? 

SELECT 
DISTINCT r.recipe_name,
(SELECT COUNT(*) FROM recipes_ingredients WHERE ingredient_id = 1 AND recipe_id = ri.recipe_id) as Tomato,
(SELECT COUNT(*) FROM recipes_ingredients WHERE ingredient_id = 2 AND recipe_id = ri.recipe_id) as Mustard,
(SELECT COUNT(*) FROM recipes_ingredients WHERE ingredient_id = 3 AND recipe_id = ri.recipe_id) as Cucumber,
(SELECT COUNT(*) FROM recipes_ingredients WHERE ingredient_id = 4 AND recipe_id = ri.recipe_id) as Flour
FROM recipes r
INNER JOIN recipes_ingredients ri on ri.recipe_id = r.id
INNER JOIN ingredients i on i.id = ri.ingredient_id
ORDER BY r.recipe_name
Fred
  • 461
  • 3
  • 9
  • 21

2 Answers2

0

Group by recipe_name and then select case max ingredient_name where ingredient_name = ingredient Something like this

SELECT 
r.recipe_name,
CASE WHEN MAX(i.ingredient_name) = 'Tomato' THEN 'Yes' ELSE 'No' END as Tomato
FROM recipes_ingredients ri
INNER JOIN recipes r on r.id = ri.recipe_id
INNER JOIN ingredients i on i.id = ri.ingredient_id
GROUP BY r.recipe_name

cases would grow with your ingredient count.

Or you can try use SQL pivot for that to be more accurate crosstab function

Sonikas
  • 131
  • 1
  • 1
  • 11
  • Thank you - I was looking for a dynamic solution as new 'ingredients' gets added all the time but I could use this - the problem is that I get 'No' in all cases, even when it should be 'yes' – Fred Nov 11 '17 at 10:18
  • use dynamic sql to generate your query dynamically with all your ingredients, this query is just how to transpone rows to columns – Sonikas Nov 11 '17 at 11:54
0

What you need is crosstab in postgresql or pivoting mssql. For a dynamic sql solution see this post (which happens to work best for me): http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ , other options, please see this question: Dynamically generate columns for crosstab in PostgreSQL

Ben
  • 1,133
  • 1
  • 15
  • 30