Convert SQL WHERE query to JOIN [closed]

I need help converting this WHERE query into JOIN. I will be following the same pattern for other queries. Thank you.

$query = "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, tbl_user u 
        WHERE r.category_id like '%{$category_name}%' 
        AND u.id = r.user_id 
        AND r.language = '$language' 
        GROUP BY r.recipe_id 
        ORDER BY r.recipe_id ASC";

Answer

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.