MYSQL Left Join single row not found in a

[Halisco]

I have two seperate tables containing an customer_id that match when a customer purchases a particular type of fruit.  I have a multi

 

 

 

table 1 "customers":

 

customer_id          email                              phone

1                           email1@email.com        555-111-1111

2                           email2@email.com        555-222-2222

3                           email3@email.com        555-333-3333

 

 

 

customer_id      type

1                        macintosh

1                        galla

2                        galla

2                        jonagold

3                        jonagold

 

 

 

Here is a shorter version of my item list i use for selecting the type of fruit i want to search for.

 

<select name=fruit_type[] multiple>
  <option value="macintosh">macintosh</option>
  <option value="gala">a</option>
  <option value="jonagold">jonagold</option>
</select>

 

I then take that list and join it

 

$search_fruit = filter_var_array($_POST['fruit_type'], FILTER_SANITIZE_SPECIAL_CHARS);
$fruit_types = join("','", $search_fruit );

Here is my SQL query 

 

$search_criteria = ""
                . "SELECT c.*  "
                . "FROM customers AS c LEFT JOIN  type AS p ON c.customer_id= p.customer_id"
                . "WHERE type NOT IN ('$fruit_types ') "
                . "GROUP BY c.customer_id, p.customer_id"
                . "ORDER BY customer_id Asc";

This works if I Just use IN for the customers i want to find that have purchased a particular type of apple but i am trying to search for customers that have never purchased a particular apple or multiple apples from my list.  For example if i select just macintosh from the list customer 1 still show up as not purchasing that apple type because they have purchased an apple that is not a macintosh.  Can anyone help me figure out how i accomplish this task?

Edited March 7, 2017 by Halisco

[taquitosensei]

select c.* from customers as c LEFT JOIN type as p on c.customer_id=p.customer_id where p.customer_id not in(select customer_id from type where type in('macintosh'));

You'll have to do a nested query to select the ones that match, then select the ones that don't match from that. 

[NigelRel3]

You could use something like 

select c.* from customers c where not exists (select 1 from type t where c.customer_id = t.customer_id and t.type in ('macintosh'))

All this does is find the people where a record isn't found for a set of types.

[taquitosensei]

You could use something like 

select c.* from customers c where not exists (select 1 from type t where c.customer_id = t.customer_id and t.type in ('macintosh'))

All this does is find the people where a record isn't found for a set of types.

Isn't that what he asked for? 

 

This works if I Just use IN for the customers i want to find that have purchased a particular type of apple but i am trying to search for customers that have never purchased a particular apple or multiple apples from my list.  For example if i select just macintosh from the list customer 1 still show up as not purchasing that apple type because they have purchased an apple that is not a macintosh.  Can anyone help me figure out how i accomplish this task?

Edited March 7, 2017 by taquitosensei