Human Life Expectancy

Red Meat vs White Meat

SELECT 
    cr.Country,
    cr.Region1,
    cr.Continent,
    le.OverallLifeExpectancy,
    le.MalesLifeExpectancy,
    le.FemalesLifeExpectancy,
    GROUP_CONCAT(DISTINCT i.Ingredient ORDER BY i.Ingredient SEPARATOR ', ') AS IngredientNames,
    MAX(i.Category) AS Category  -- Get the category for grouping
FROM 
    country_region cr
    INNER JOIN 
    country_national_dish cnd ON cr.Country_ID = cnd.Country_ID
    INNER JOIN 
    dishes d ON cnd.Dishes_ID = d.Dishes_ID
    INNER JOIN 
    main_contents mc ON d.Dishes_ID = mc.Dishes_ID
    INNER JOIN 
    ingredients i ON mc.Ingredient_ID = i.Ingredient_ID
    INNER JOIN 
    life_expectancy le ON cr.Country_ID = le.Country_ID
WHERE 
    i.Category IN ('Red meat', 'White meat')
GROUP BY 
    cr.Country,
    cr.Region1,
    cr.Continent,
    le.OverallLifeExpectancy,
    le.MalesLifeExpectancy,
    le.FemalesLifeExpectancy
HAVING 
    COUNT(DISTINCT i.Category) = 1;  -- Ensures only one category (Red meat or White meat) is present

SQL Result
Country	Region1	Continent	Overall Life Expectancy	Males Life Expectancy	Females Life Expectancy	Ingredient Names	Category
Albania	Southern Europe	Europe	79.9	77.3	82.8	Lamb	Red Meat
Angola	Middle Africa	Africa	62.9	60.8	65.1	Chicken	White Meat
Armenia	Western Asia	Asia	76.7	73.4	80.1	Meat	Red Meat
Australia	Australia and New Zealand	Oceania	83.5	81.3	85.7	Meat	Red Meat
Austria	Western Europe	Europe	82.7	80.1	85.4	Veal	White Meat
Azerbaijan	Western Asia	Asia	75.9	73.5	78.6	Lamb	Red Meat
Bahamas	Caribbean	North America	76.7	75.1	78.4	Crayfish	White Meat
Bahrain	Western Asia	Asia	80.4	78.1	82.7	Chicken	White Meat
Bangladesh	Southern Asia	Asia	75.2	73.1	77.5	Fish	White Meat
...	...	...	...	...	...	...	...