Mysql查找result_id where name = "x"然后删除共享该result_id和 - Mysql find result_id where name = "x" then delete other entries that share that result_id and name="y"

Mysql find result_id where name = "x" then delete other entries that share that result_id and name="y"

我使用php和mysql的混合物做以下查询，我相信必须有一种方法来做它只是SQL。我已经剥离了一些代码，并留下了足够的代码，您应该能够看到我正在尝试完成什么。

任何帮助将非常感激!

SELECT result_id FROM categories
WHERE name = "conventional"

foreach ( $results as $row ){
    $query = "
    DELETE FROM categories
    WHERE result_id = $row->result_id && (name = 'va' OR name = 'fha' OR name = 'usda' OR name = 'other')
    ";
    $this->db($query);
}

编辑:从我目前收到的答案来看，我认为我解释得不够好。该表的列为id, result_id, name

有多个表项共享相同的result_id，因为它们与"results"表中的表项相关。因此，我试图找到名称为"conventional"的条目的result_id因此，我可以找到具有相同result_id的所有条目并删除它们，如果它们的名称是"va" "fha" "usda"或"other"

DELETE FROM categories 
WHERE name IN ("va", "fha", "usda", "other")
AND result_id IN (
  SELECT result_id FROM categories
  WHERE name = 'conventional'
)

你想避免在循环中删除查询，下面是如何做到这一点:

SELECT result_id FROM categories
WHERE name = "conventional"
// Now build csv of ids    
$arx = array();
foreach ( $results as $row ) {
    $arx[] = $row->result_id;
} 
if(!empty($arx)) {
    $arx_1 = implode(',', $arx);
    $query = "DELETE FROM categories
    WHERE result_id in ({$arx_1}) AND (name = 'va' OR name = 'fha' OR name = 'usda' OR name = 'other')";
    $this->db($query);
}