使用 Select 进行内部联接的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10024295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query for Inner Join with Select
提问by user1157690
I want to write a SQL query like the following. Its syntax is not correct. How can I correct it?
我想编写如下的 SQL 查询。它的语法不正确。我该如何纠正？
$sql_package_feature = "SELECT f.feature_id, f.feature_name FROM tbl_feature f
LEFT JOIN SELECT * FROM tbl_feature_and_profile fp WHERE fp.profile_id= ? ) ON
f.feature_id = fp.feature_id AND f.package_id = fp.package_id WHERE fp.feature_id
IS NULL AND f.package_id = ? ORDER BY f.feature_id";
回答by Adrian Serafin
I think it was abount missing 'as fp' after subselect. Try this query:
我认为在子选择之后几乎没有“作为 fp”。试试这个查询：
SELECT
f.feature_id,
f.feature_name
FROM tbl_feature f
LEFT JOIN (SELECT * FROM tbl_feature_and_profile fp WHERE fp.profile_id= ? )
as fp ON (f.feature_id = fp.feature_id AND f.package_id = fp.package_id)
WHERE
fp.feature_id IS NULL AND f.package_id = ? ORDER BY f.feature_id
回答by Guffa
If you join against a subselect, you have to name it. Put the name on the subselect instead of the table inside it:
如果您针对子选择加入，则必须为其命名。将名称放在子选择上而不是其中的表上：
SELECT f.feature_id, f.feature_name
FROM tbl_feature f
LEFT JOIN (
SELECT *
FROM tbl_feature_and_profile
WHERE profile_id= ?
) fp ON f.feature_id = fp.feature_id AND f.package_id = fp.package_id
WHERE fp.feature_id IS NULL AND f.package_id = ?
ORDER BY f.feature_id
回答by Hubert Sch?lnast
You didn't give a name to the second table, but you are using it later in the ON-Clause. fpwas missing after the closing bracket:
您没有为第二个表命名，但稍后在 ON-Clause 中使用它。fp在结束括号后丢失：
SELECT f.feature_id, f.feature_name
FROM tbl_feature f
LEFT JOIN (
SELECT *
FROM tbl_feature_and_profile fp
WHERE fp.profile_id= ?
) fp
ON f.feature_id = fp.feature_id
AND f.package_id = fp.package_id
WHERE fp.feature_id IS NULL
AND f.package_id = ?
ORDER BY f.feature_id"
;
回答by Byron
Try a JOIN without a nested SELECT statement, only a table name. Try:
尝试一个没有嵌套 SELECT 语句的 JOIN，只有一个表名。尝试：
$sql_package_feature =
"SELECT f.feature_id, f.feature_name
FROM
tbl_feature f
LEFT JOIN
tbl_feature_and_profile fb
ON f.feature_id = fp.feature_id AND f.package_id = fp.package_id
WHERE fp.feature_id IS NULL AND f.package_id = ? AND fp.profile_id = ? ORDER BY f.feature_id";
