[Solved] Convert SQL WHERE query to JOIN [closed]

by


For the most part, you just change your comma separated tables to JOIN clauses. The “ON” clause of the join is what comes from the WHERE clause

SELECT DISTINCT 
      r.recipe_id, 
      r.recipe_name, 
      r.recipe_image,
      r.recipe_duration, 
      r.recipe_serving, 
      r.recipe_difficulty, 
      (SELECT category_name 
          FROM tbl_category 
          WHERE r.category_id = category_id) AS category_name, 
      (SELECT cuisine_name 
          FROM tbl_cuisine 
          WHERE r.cuisine_id = cuisine_id) AS cuisine_name, 
      r.recipe_ingredients, 
      r.recipe_steps, 
      r.date, 
      r.user_id, 
      u.fname, 
      u.lname, 
      u.image, 
      r.language 
   FROM 
      tbl_recipe r
         JOIN tbl_user u 
            on r.user_id = u.id
   WHERE 
          r.category_id like '%{$category_name}%' 
      AND r.language="$language" 
   GROUP BY 
      r.recipe_id 
   ORDER BY 
      r.recipe_id ASC

Also, instead of column-based select, you could pullthe category and cuisine joined as well. More efficient than query for every record

SELECT DISTINCT 
      r.recipe_id, 
      r.recipe_name, 
      r.recipe_image,
      r.recipe_duration, 
      r.recipe_serving, 
      r.recipe_difficulty,
      cat.category_name, 
      cn.cuisine_name, 
      r.recipe_ingredients, 
      r.recipe_steps, 
      r.date, 
      r.user_id, 
      u.fname, 
      u.lname, 
      u.image, 
      r.language 
   FROM 
      tbl_recipe r
         JOIN tbl_user u 
            on r.user_id = u.id
         JOIN tbl_category cat
            on r.category_id = cat.category_id 
         JOIN cuisine_name cn
            on r.cuisine_id = cn.cuisine_id 
   WHERE 
          r.category_id like '%{$category_name}%' 
      AND r.language="$language" 
   GROUP BY 
      r.recipe_id 
   ORDER BY 
      r.recipe_id ASC

Also notice, from left-to-right my “join/on” conditions are listing the left table/alias = what I am joining TO table/alias. Helps keep directional awareness of from here to there the tables are joined.

In addition as other person noted in comment, you are open to SQL-Injection and should always parameterize your queries and clean your input source values… So research into SQL-Injection and data cleansing too.

1

solved Convert SQL WHERE query to JOIN [closed]