如何按两个 AND 条件过滤一列?

Question

问题已解决。但是，如果您有"更好"或其他方法，请随时添加评论！感谢大家的阅读！:)

我正在尝试进行动态查询。除了一件事，一切都很完美。我已经谷歌了好几天，但我不知道如何使以下工作;

SELECT project.name, project.description, track.name, track.description
, SDG.position, SDG.title, SDG.description
, sprint_numbers.number, sprint_options.option
, resources.name, resources.description
, URLs.URL 
FROM project INNER JOIN track ON project.track_id = track.id 
INNER JOIN project_SDG ON project.id = project_SDG.project_id 
INNER JOIN SDG ON project_SDG.SDG_id = SDG.id
INNER JOIN sprint ON sprint.project_id = project.id 
INNER JOIN sprint_numbers ON sprint_numbers.id = sprint.sprint_number_id 
INNER JOIN sprint_options ON sprint_options.id = sprint.sprint_option_id 
INNER JOIN resources ON project.id = resources.project_id 
INNER JOIN URLs ON URLs.id = resources.id 
WHERE 1=1 
AND MATCH (project.name) AGAINST (:name_project) 
AND MATCH (project.description) AGAINST (:description_project) 
AND SDG.id = :SDG_1 
AND SDG.id = :SDG_2

查询将执行，但不返回任何内容。问题是 SDG.id 不能同时对 ：SDG_1 和 ：SDG_2 都为真。

使用 OR 运算符有效，但这不会以我想要的方式返回它。它必须"充当"AND 运算符。(：SDG_1 和 ：SDG_2 是绑定到 SQL 语句参数的 PHP 变量的名称。

查询应筛选这两个值。为 ：SDG_1 和 ：SDG_2 提供的值必须都存在于project_SDG表的 SDG.id 列中。如果 ：SDG_1 的值存在，但 ：SDG_2 不存在，则查询不应返回任何内容。

我在 StackOverflow 上找到了这个，但它对我不起作用：在同一列上使用多个 WHERE 条件进行选择

我希望有人能帮助我。

编辑：最小的可重现示例

查询：

SELECT * FROM project
INNER JOIN project_SDG ON project.id = project_SDG.project_id 
INNER JOIN SDG ON project_SDG.SDG_id = SDG.id
WHERE SDG.id = 1 AND SDG.id = 7 AND SDG.id = 14 AND SDG.id = 17

项目表

+------------------+---------------------------+------------+
|     id  name     |        description        |  track_id  |
+------------------+---------------------------+------------+
| 1   project name | This is a description   2 |            |
+------------------+---------------------------+------------+

可持续发展目标表

+-----+-----------+-------------+---------------------------------------------+
| id  | position  |   title     |                 description                 |
+-----+-----------+-------------+---------------------------------------------+
|   1 |         1 | SDG 1 to 17 | There're multiple SDGs ranging from 1 to 17 |
|  17 |        17 | SDG 1 to 17 | There're multiple SDGs ranging from 1 to 17 |
+-----+-----------+-------------+---------------------------------------------+

项目。可持续发展目标(桥接表(

+------------+--------+
| project.id | SDG.id |
+------------+--------+
|          1 |      1 |
|          1 |      2 |
|          1 |      3 |
+------------+--------+

Answer 1

您希望每个值project.id:SDG_1和:SDG_2都存在SDG.id，因此请在WHERE子句中使用它：

WHERE 1=1 
AND MATCH (project.name) AGAINST (:name_project) 
AND MATCH (project.description) AGAINST (:description_project) 
AND project.id IN (
SELECT project_id
FROM project_SDG
WHERE SDG_id IN (:SDG_1, :SDG_2)
GROUP BY project_id
HAVING COUNT(DISTINCT SDG_id) = 2
) 

Answer 2

你能为你的查询提供一个最小的可重现的例子吗？
一般来说，一个字段不能同时等于两个不同的值。因此，您要么混淆了逻辑运算符，要么需要两个不同的字段。

我可以假设在您的情况下，可能有几个具有不同值的相关记录。在这种情况下，您需要使用不同的别名联接同一表两次。让我们说SDG1和SDG2.之后你可以比较

... `SDG1`.id = :SDG_1 AND `SDG2`.id = :SDG_2

更新：

制胜的诀窍是分组。您可以枚举所有必需的 SDG ID，并计算组中有多少个 ID。例如，在两个ID的情况下：

SELECT project.id
FROM project
JOIN project_SDG ON project_SDG.project_id = project.id
JOIN SDG ON SDG.id = project_SDG.SDG_id
WHERE SDG.id IN(1,2)
GROUP BY project.id
HAVING COUNT(*) = 2

在这里查看我的沙盒：https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/0

如果您需要所有项目的字段，则必须将其放入子查询中，作为

... WHERE id IN ( subquery here )

子查询示例：https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/1

Answer 3

我已经在这里回答了，但我有另一个合适的方法。

1. 查找与某个项目关联的一堆 ID

要查找项目 ID，我们可以在没有任何连接的情况下测试孤独的数据透视表：

SELECT project_id FROM project_SDG
WHERE SDG_id IN(1,2,6)
GROUP BY project_id HAVING COUNT(*) = 3

它为我们提供了项目 ID 列表

2. 访问所有项目字段并添加额外条件

SELECT project.*
FROM project
JOIN (
SELECT project_id FROM project_SDG
WHERE SDG_id IN(1,2,6)
GROUP BY project_id HAVING COUNT(*) = 3
) AS ids ON ids.project_id = project.id
WHERE
MATCH(project.name) AGAINST ('project') AND
MATCH(project.description) AGAINST ('sit')

你可以在这里玩它：https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/3

3. 在 PHP 端准备查询

我将使用已知的技术来准备 SQL 语句。

$ids = [1, 2, 6]; // it can come from request parameters
$text1 = 'project';
$text2 = 'sit';
// build ?,?,?,... pattern
$qmarks = implode(',', array_fill(0, count($ids), '?'));
// Use SQL query above
$sth = $dbh->prepare("
SELECT project.*
FROM project
JOIN (
SELECT project_id FROM project_SDG
WHERE SDG_id IN({$qmarks})
GROUP BY project_id HAVING COUNT(*) = ?
) AS ids ON ids.project_id = project.id
WHERE
MATCH(project.name) AGAINST (?) AND
MATCH(project.description) AGAINST (?)
");
$sth->execute(array_merge($ids, [count($ids), $text1, $text2]));
$records = $sth->fetchAll();