Mysql – How to get desired result set

join;MySQL

I have the following two tables
customers

| customer_id | firstname | lastname | email                |
|-------------|-----------|----------|----------------------|
| 1001        | John      | Doe      | johndoe@gmail.com    |
| 1002        | Lorem     | Ipsum    | loremipsum@gmail.com |
| 1003        | Alph      | Beta     | alphabeta@gmail.com  |

And orders 

| order_id | customer_id | via | status     |
|----------|-------------|-----|------------|
| 100      | 1001        | WEB | pending    |
| 101      | 1001        |     | pending    |
| 102      | 1001        | POS | pending    |
| 103      | 1002        | WEB | processing |
| 104      | 1002        | APP | pending    |
| 105      | 1001        | APP | completed  |
| 106      | 1003        |     | pending    |

I want to get the following resultset:

  1. All the customers who have ordered via WEB or (EMPTY) but not from POS
  2. All the customers who have ordered only via APP

Best Answer

  1. You can use an inner join between customers and orders where the via has the wanted values and a NOT EXISTS to check, that no unwanted via exists.

    SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via IN ('WEB', '')
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via = 'POS');

    (If the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)

    Alternatively you can do it just with EXISTS and NOT EXISTS. 

    SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via IN ('WEB', ''))
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via = 'POS');

    (Again, if the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)

  2. Again you can use a join and NOT EXISTS.

    SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via = 'APP'
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via <> 'APP');

    Or just use EXISTS/NOT EXISTS.

    SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via = 'APP')
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via <> 'APP');
Related Question