mySQL跨多行搜索返回不同的id

Question

我在子表中搜索时遇到问题。这是一个简单的例子:

表:project_details

我想按项目名称、描述搜索项目，然后将其与project_list_id(例如邮政编码和状态)关联起来。

我知道问题出在我搜索详细表格的方式上。所以我把它分解为只有一张表的更简单的东西:

SELECT `ProjectDetail`.`project_list_id` FROM `database`.`project_details` AS `ProjectDetail` WHERE (
    (
        (
            (`ProjectDetail`.`answer` = '96720') 
            AND 
            (`ProjectDetail`.`field_name` = 'zip')
        )
    ) 
    AND (
        (
            (`ProjectDetail`.`answer` = 'HI') 
            AND 
            (`ProjectDetail`.`field_name` = 'state')
        )
    ) 
)

我想选择满足这两个字段要求的project_list_id，但由于它们是唯一行，因此不会返回任何内容。

Answer 1

据我了解，您不想返回所有满足这两个条件的每个 id 的projects_ids。所以这应该有效:

SELECT a.project_list_id 
FROM database.project_details a
INNER JOIN database.project_details b
ON a.project_list_id = b.project_list_id
WHERE a.answer = '96720' AND a.field_name = 'zip'
AND b.answer = 'HI' AND b.field_name = 'state'