Got this now, but does not do anything, what is wrong? Any help is highly appreciated.
global $wpdb;
// delete all posts WHERE meta_value = $aid;
$wpdb->query("DELETE wp
FROM $wpdb->posts wp
LEFT JOIN $wpdb->postmeta pm ON pm.post_id = wp.ID
WHERE mp.meta_key = 'product_aid' AND mp.meta_value = '". $aid ."'");
// delete not used meta
$wpdb->query("DELETE pm
FROM $wpdb->postmeta pm
LEFT JOIN $wpdb->posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL");
Figured it out myself
global $wpdb;
$querystr = "SELECT post_id, count(post_id)
FROM $wpdb->postmeta
WHERE
(meta_key = 'product_aid' AND meta_value = '". $aid ."') OR
(meta_key = 'product_id' AND meta_value = '". (string)$item->productID ."')
GROUP BY post_id
HAVING count(post_id) = 2;
";
$postid = $wpdb->get_var($wpdb->prepare($querystr));
