SQL Browser Crashing when running a Nested Query

I have a database with three tables:

  1. grocery
  2. nutrient
  3. grocery_nutrient (for many to many)

Actually, the database is very big and I am facing the problems by running this query, the SQL browser crashes when I run, if I add LIMIT it works.

enter image description here

enter image description here

enter image description here

and this is the query I have written for it.

SELECT 
  grocery.*, 
  (
    SELECT 
      grocery_nutrient.amount || " " || grocery_nutrient.unit 
    FROM 
      grocery_nutrient 
    WHERE 
      grocery_nutrient.nutrient_id = 2 
      AND grocery.grocery_id = grocery_nutrient.grocery_id
  ) AS calories, 
  (
    SELECT 
      grocery_nutrient.amount || " " || grocery_nutrient.unit 
    FROM 
      grocery_nutrient 
    WHERE 
      grocery_nutrient.nutrient_id = 6 
      AND grocery.grocery_id = grocery_nutrient.grocery_id
  ) AS fats, 
  (
    SELECT 
      grocery_nutrient.amount || " " || grocery_nutrient.unit 
    FROM 
      grocery_nutrient 
    WHERE 
      grocery_nutrient.nutrient_id = 3 
      AND grocery.grocery_id = grocery_nutrient.grocery_id
  ) AS protein, 
  (
    SELECT 
      grocery_nutrient.amount || " " || grocery_nutrient.unit 
    FROM 
      grocery_nutrient 
    WHERE 
      grocery_nutrient.nutrient_id = 4 
      AND grocery.grocery_id = grocery_nutrient.grocery_id
  ) AS carbs 
FROM 
  grocery 
  LEFT JOIN grocery_nutrient ON grocery.grocery_id = grocery_nutrient.grocery_id
GROUP BY 
  grocery.grocery_id

1 answer

  • answered 2021-07-27 11:48 forpas

    You can simplify your query if you use conditional aggregation:

    SELECT g.*, 
           MAX(CASE WHEN n.nutrient_id = 2 THEN n.amount || ' ' || n.unit END) AS calories, 
           MAX(CASE WHEN n.nutrient_id = 6 THEN n.amount || ' ' || n.unit END) AS fats, 
           MAX(CASE WHEN n.nutrient_id = 3 THEN n.amount || ' ' || n.unit END) AS protein, 
           MAX(CASE WHEN n.nutrient_id = 4 THEN n.amount || ' ' || n.unit END) AS carbs
    FROM grocery g LEFT JOIN grocery_nutrient n 
    ON g.grocery_id = n.grocery_id
    GROUP BY g.grocery_id
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum