flavours-without-borders/Database implementation/views/view_1_as_query.sql

SELECT `restaurant`.`restaurant_id`,
    `restaurant`.`restaurant_name`,
    `restaurant`.`restaurant_category`,
    `restaurant`.`restaurant_longitude`,
    `restaurant`.`restaurant_latitude`,
    `restaurant`.`restaurant_opening`,
    `restaurant`.`restaurant_closing`,
    `restaurant`.`user_id`,
	NULL AS `food_id`,
    NULL AS `food_name`,
    NULL AS `food_description`,
    NULL AS `food_calories`,
    NULL AS `drink_id`,
    NULL AS `drink_name`,
    NULL AS `drink_description`,
    NULL AS `ingredient_name`,
    NULL AS `ingredient_has_alcohol`
FROM flavours_without_borders.restaurant
WHERE restaurant.restaurant_is_approved = TRUE
UNION
SELECT `restaurant`.`restaurant_id`,
    `restaurant`.`restaurant_name`,
    `restaurant`.`restaurant_category`,
    `restaurant`.`restaurant_longitude`,
    `restaurant`.`restaurant_latitude`,
    `restaurant`.`restaurant_opening`,
    `restaurant`.`restaurant_closing`,
    `restaurant`.`user_id`,
	`food`.`food_id`,
    `food`.`food_name`,
    `food`.`food_description`,
    `food`.`food_calories`,
    NULL AS `drink_id`,
    NULL AS `drink_name`,
    NULL AS `drink_description`,
    `ingredient`.`ingredient_name`,
    `ingredient`.`ingredient_has_alcohol`
FROM flavours_without_borders.restaurant
LEFT JOIN flavours_without_borders.food
	ON restaurant.restaurant_id = food.restaurant_id
LEFT JOIN flavours_without_borders.food_has_ingredient
	ON food.food_id = food_has_ingredient.food_id
LEFT JOIN flavours_without_borders.ingredient
	ON food_has_ingredient.ingredient_name = ingredient.ingredient_name
WHERE restaurant.restaurant_is_approved = TRUE AND food.food_is_approved = TRUE
UNION
SELECT `restaurant`.`restaurant_id`,
    `restaurant`.`restaurant_name`,
    `restaurant`.`restaurant_category`,
    `restaurant`.`restaurant_longitude`,
    `restaurant`.`restaurant_latitude`,
    `restaurant`.`restaurant_opening`,
    `restaurant`.`restaurant_closing`,
    `restaurant`.`user_id`,
    NULL AS `food_id`,
    NULL AS `food_name`,
    NULL AS `food_description`,
    NULL AS `food_calories`,
	`drink`.`drink_id`,
    `drink`.`drink_name`,
    `drink`.`drink_description`,
    `ingredient`.`ingredient_name`,
    `ingredient`.`ingredient_has_alcohol`
FROM flavours_without_borders.restaurant
LEFT JOIN flavours_without_borders.drink
	ON restaurant.restaurant_id = drink.restaurant_id
LEFT JOIN flavours_without_borders.drink_has_ingredient
	ON drink.drink_id = drink_has_ingredient.drink_id
LEFT JOIN flavours_without_borders.ingredient
	ON drink_has_ingredient.ingredient_name = ingredient.ingredient_name
WHERE restaurant.restaurant_is_approved = TRUE AND drink.drink_is_approved = TRUE