SQL request

[Lambert_TMC]

Hello,

I am trying to change a form module . I need to retrieve email addresses of persons who have not responded .
I made 2 tables :
' Submissions it contains the answers forms
'customer' which contains all customer information.

So I create multiple SQL queries to retrieve the id of those who responded to retrieve the id of those who receive my forms ( the group with id 4). I would like to retrieve the addresses of people who have not responded .

 

These are my queries :

// retrieves the id of customer who responded
$mysql = 'SELECT `id_customer`
          FROM `'._DB_PREFIX_.'ukooformpro_submission` 
          WHERE `id_ukooformpro_form` = '.(int)$id;
$id_customer_reply = Db::getInstance()->ExecuteS($mysql);

// retrieves the id of all customer who received form
$mysql3 = 'SELECT `id_customer`
           FROM `'._DB_PREFIX_.'customer`
           WHERE `id_default_group` = '.(int)4;
$id_all_revendeurs = Db::getInstance()->ExecuteS($mysql3);

Both queries work but I can not retrieve customers who have not responded

 

Could someone help me please ?

 

Thanks !

[bellini13]

add email to your second query, and then take the results (id_customers) of your first query, and filter them out of your second query.

[Lambert_TMC]

Hello Belliny13,

Thank you for your answer.
I just find the solution , here is the query I used :

$mysql = 'SELECT `email`, `firstname`, `lastname` 
        FROM `'._DB_PREFIX_.'customer` 
        WHERE `id_customer` NOT IN 
              (SELECT c.`id_customer` 
               FROM `'._DB_PREFIX_.'customer` AS c 
               NATURAL JOIN `'._DB_PREFIX_.'ukooformpro_submission` AS s 
               WHERE s.`id_ukooformpro_form` = '.(int)$id.');
$results = Db::getInstance()->ExecuteS($mysql);

it works perfectly and it's still pretty fast (it is a query that is called in Ajax)

 

Bye