SQL join select based on specific table values

Goal: Select all items if the users_allergy =0 but filter items out when the allergy=1

User_peanut_allergy = 0 means the user has no peanut allergy.

User_peanut_allergy = 1 means the user has that allergy. Do not display nuts

An allergy item is set to 1 in the user table and nutrition table. If the user has no allergies then the table value is 0.

This works if a user has no allergies but fails when there is an allergy (set to 1). It fails since >=0. I need to distinguish matching greater than 0 but not when the users allergy is a 1.

SELECT *
FROM [dbo].[Nutrition]
INNER JOIN Users ON Nutrition.contains_gluten >= Users.gluten_allergy
    AND Nutrition.contains_nuts >= Users.peanut_allergy
    AND Nutrition.contains_fish >= Users.fish_allergy
    AND Nutrition.contains_soy >= Users.soy_allergy
    AND Nutrition.contains_dairy >= Users.dairy_allergy
WHERE Users.username = 'Kyle';

How can I put an if statement to determine if the user doesn’t have an allergy then all items are displayed?

IF (Users.peanut_allergy == 0)
    SELECT everything including allergy items
ELSE
    JOIN
    WHERE contains_nuts = 0

Sample Data: https://pastebin.com/2knYTcHk Test a User table with 0 for no allergy and 1 for allergy

Answer

I’m still not sure I understand exactly what you are looking for, but try something like this:

SELECT * from [dbo].[Nutrition]
INNER JOIN Users
 ON (Users.gluten_allergy = 0 or Nutrition.contains_gluten = 0)
AND (Users.peanut_allergy = 0 or Nutrition.contains_nuts = 0)
AND (Users.fish_allergy = 0 or Nutrition.contains_fish = 0)
AND (Users.soy_allergy = 0 or Nutrition.contains_soy = 0)
AND (Users.dairy_allergy = 0 or Nutrition.contains_dairy = 0)
where Users.username = 'Kyle';

Each one follows the same pattern. Either the User has no allergy, and the condition is true, or the food in the Nutrition table doesn’t contain that ingredient. In other words, the only way that the join fails is if the user has the allergy and the food contains that ingredient.