conan conan - 4 months ago 12
MySQL Question

Two SELECT inside NOT IN echo array() mysql php, one SELECT IS OK

my code is OK only if I remove one (SELECT) inside the NOT IN, if I add two (SELECT) inside the NOT IN, it echo array(); First, I want to get the fruit that val=those value and mark = 'major'. Then I want to remove any of those fruit that either the mark='age' or mark='ripe', so I used OR fruit NOT IN. I already check the SELECT inside the NOT IN, it is OK when they are alone.



<?php
$dia = $wpdb->get_results( $wpdb->prepare("
SELECT fruit FROM table WHERE val IN (%s,%s,%s) AND mark = 'major' OR fruit NOT IN (
(SELECT fruit FROM table WHERE cat='uncommon' AND val = %s AND mark='age'),
(SELECT fruit FROM table WHERE cat='uncommon' AND val = %s AND mark='ripe')) GROUP BY fruit
",$sym1,$sym2,$sym3,$age,$ripe));



?>




Answer

You don't need two queries for this, you just need to alter your conditions. Change:

(SELECT fruit FROM table WHERE cat='uncommon' AND val = %s AND mark='age')

To:

(SELECT fruit FROM table WHERE cat='uncommon' AND (val = %s AND mark='age') OR (val = %s AND mark='ripe'))

Otherwise you'd need an aggregate query.