Btukfyl

I'm trying to compose an SQLite query and I've found a problem that's beyond my skillset. I'm trying to output columns that are based on the rows of another referenced table.

Food_List:

| food_id | name      |

|---------|-----------|

|       1 | Apple     |

|       2 | Orange    |

|       3 | Pear      |

Nutrient_Definition:

| nutrient_id | name      |

|-------------|-----------|

|          21 | Carbs     |

|          22 | Protein   |

|          23 | Fat       |

Nutrient_Data:

| food_id | nutrient_id | value |

|---------|-------------|-------|

|       1 |          21 |    50 |

|       1 |          22 |    24 |

|       1 |          23 |    63 |

|       2 |          22 |    12 |

|       2 |          23 |    95 |

|       3 |          21 |    66 |

|       3 |          22 |    87 |

|       3 |          23 |    38 |

Output:

| food_id | name      | Carbs | Protein | Fat |

|---------|-----------|-------|---------|-----|

|       1 | Apple     |    50 |      24 |  63 |

|       2 | Orange    |       |      12 |  95 |

|       3 | Pear      |    66 |      87 |  38 |

(Note that Orange does not have a "Carbs" entry in the Nutrient_Data table)

I believe the following will do what you want :-

DROP TABLE IF EXISTS food_list;

CREATE TABLE IF NOT EXISTS food_list(food_id INTEGER PRIMARY KEY, name TEXT);

DROP TABLE IF EXISTS nutrient_definition;

CREATE TABLE IF NOT EXISTS nutrient_definition(nutrient_id INTEGER PRIMARY KEY, name TEXT);

DROP TABLE IF EXISTS nutrient_data;

CREATE TABLE IF NOT EXISTS nutrient_data(food_id INTEGER, nutrient_id INTEGER, value INTEGER);



INSERT INTO food_list (name) VALUES

    ('apple'),('orange'),('pear')

;

INSERT INTO nutrient_definition (name) VALUES

    ('carbs'),('protien'),('fat')

;

INSERT INTO nutrient_data VALUES

    (1,1,50),(1,2,24),(1,3,63),

    (2,2,12),(2,3,95),

    (3,1,66),(3,2,87),(3,3,38)

;

SELECT food_list.food_id,food_list.name, 



    (

    SELECT value 

    FROM nutrient_data 

    WHERE nutrient_data.food_id = food_list.food_id AND 

        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'carbs')

    ),

    (

    SELECT value 

    FROM nutrient_data 

    WHERE nutrient_data.food_id = food_list.food_id AND 

        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'protien')

    ),

    (

    SELECT value 

    FROM nutrient_data 

    WHERE nutrient_data.food_id = food_list.food_id AND 

        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'fat')

    )



 FROM food_list

 ;

Results in :-