This is my database. FoodItems is just normal table but I want to use virtual columns to generate all the related data, e.g. Servings.kcal is equal to related (FieldItem.kcal*unit)/100 (the data is specified in kilocalories per 100g/100ml):
CREATE TABLE IF NOT EXISTS `DietDB`.`Servings` ( `id` INT UNSIGNED NOT NULL, `FoodItem_id` INT UNSIGNED NOT NULL, `name` VARCHAR(255) NOT NULL, `units` DOUBLE UNSIGNED NULL, `kcal` INT GENERATED ALWAYS AS (SELECT (FoodItems.kcal*units)/100 FROM Servings JOIN FoodItems ON Servings.FoodItem_id = FoodItems.id) VIRTUAL, `carbohydrate` DOUBLE UNSIGNED NULL, `carbohydrate_sugar` DOUBLE UNSIGNED NULL, `fat` DOUBLE UNSIGNED NULL, `fat_saturated` DOUBLE UNSIGNED NULL, `fat_trans` DOUBLE UNSIGNED NULL, `protein` DOUBLE UNSIGNED NULL, `fibre` DOUBLE UNSIGNED NULL, PRIMARY KEY (`id`, `FoodItem_id`), INDEX `fk_Servings_FoodItems1_idx` (`FoodItem_id` ASC), UNIQUE INDEX `name_UNIQUE` (`name` ASC), CONSTRAINT `fk_Servings_FoodItems1` FOREIGN KEY (`FoodItem_id`) REFERENCES `DietDB`.`FoodItems` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB
This is obviously wrong as the query returns the whole table, not single value and it yells an error... but it presents what I'm trying to achieve there. How could it be done?