时间戳

首页 > 解决方案 > 将 SQL WHERE 查询转换为 JOIN

mysql - 将 SQL WHERE 查询转换为 JOIN

问题描述

我需要帮助将此 WHERE 查询转换为 JOIN。对于其他查询，我将遵循相同的模式。谢谢你。

$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";

标签： mysqlsqldatabase

解决方案

在大多数情况下，您只需将逗号分隔的表更改为 JOIN 子句。连接的“ON”子句来自 WHERE 子句

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

此外，除了基于列的选择之外，您还可以拉出类别和加入的美食。比查询每条记录更高效

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

另请注意，从左到右，我的“加入/开启”条件列出了左表/别名 = 我要加入的 TO 表/别名。有助于保持从这里到那里连接表格的方向意识。

此外，正如其他人在评论中指出的那样，您对 SQL 注入持开放态度，并且应该始终参数化您的查询并清理您的输入源值......因此也要研究 SQL 注入和数据清理。

推荐阅读