How to get desired result set?

Question

I have the following two tables
customers

| customer_id | firstname | lastname | email                |
|-------------|-----------|----------|----------------------|
| 1001        | John      | Doe      | [email protected]    |
| 1002        | Lorem     | Ipsum    | [email protected] |
| 1003        | Alph      | Beta     | [email protected]  |

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

Answer 1

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');