编写一条sql语句，根据其他表中的列查询一个表 - Write a sql statement to query one table based on columns in other tables?

Write a sql statement to query one table based on columns in other tables?

我有三个表：pics、folders和pics_by_folders。

图片

 pic_ID | name | info

文件夹

folder_ID | name | info

pics_by_folders

folder_ID | pic_ID

我试图返回所有满足要求x、y、z的pics，但前提是它们包含在满足要求j、k、l的文件夹中。我希望我能以一种有意义的方式来解释这一点。我目前的说法是：

SELECT * FROM pics WHERE 
              pic_ID = 'x' AND 
              pics.name = 'y' AND 
              pics.info = 'Z'
JOIN pics_by_folder as pbf ON 
              pbf.pic_ID = pics.pic_ID
JOIN folders WHERE 
              folder_ID = 'j' AND 
              folders.name = 'k' AND 
              folders.info = 'l'
ON
              folders.folder_ID = pbf.folder_ID

这不起作用，我认为这与语句的顺序有关，或者sql不允许"JOIN x WHERE ..."语句。无论哪种方式，任何帮助都将不胜感激。

语法错误

SELECT 
 * 
FROM pics 
JOIN pics_by_folder as pbf ON pbf.pic_ID = pics.pic_ID
JOIN folders ON folders.folder_ID = pbf.folder_ID
WHERE folder_ID = 'j' AND 
   folders.name LIKE 'k' AND 
   folders.info LIKE 'l' AND
   pic_ID LIKE 'x' AND 
   pics.name LIKE 'y' AND 
   pics.info LIKE 'Z'

一些注意事项：

请始终指定结果集中需要的列。否则，您会感到困惑，因为您不知道结果集中会有多少列。

folders.name LIKE 'k'相当于folders.name = 'k'

如果您想要完全匹配，则使用folders.name ='k'，否则使用通配符运算符%，如下所示：folders.name LIKE '%k%'

不清楚你想做什么。但你试过了吗。

SELECT * FROM pics WHERE 
              pic_ID LIKE 'x' AND 
              pics.name LIKE 'y' AND 
              pics.info LIKE 'Z'
JOIN pics_by_folder as pbf ON 
              pbf.pic_ID = pics.pic_ID
JOIN folders on               folders.folder_ID = pbf.folder_ID and
              folder_ID = 'j' AND 
              folders.name LIKE 'k' AND 
              folders.info LIKE 'l'

你是对的，那是错误的sintaxis

检查手册

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references]   --------------- THIS INCLUDE ALL JOIN
    [WHERE where_condition]   --------------- ALL RESTRICTION TOGETHER
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]

您可以编写这样的语句

select * from pics where pic_id like 'x' and pics.name like 'y' and 
pics.info like 'z' and pic_id in 
(select pic_id from pics_by_folders where folder_id in 
(select folder_id from folders where folder_id ='j' and 
folders.name like 'k' and folders.info like 'l'))