MySQL 8.0 – SELECT QUERY

mysql-workbenchqueryselect

Hi I am having trouble with building a select query command with my tables.

TABLE CUSTOMER

+-----------------+-----------------+--------------+
| customer_number | customer_status | order_status |
+-----------------+-----------------+--------------+
|     196         |      unpaid     |    served    |
+-----------------+-----------------+--------------+
|     197         |      unpaid     |  unserved    |
+-----------------+-----------------+--------------+
|     198         |       paid      |    served    |
+-----------------+-----------------+--------------+

TABLE CUSTOMER_ORDERS

+-----------------+-----------------+--------------+
| cust_ord_number | customer_number | order_status |
+-----------------+-----------------+--------------+
|       350       |       196       |  preparing   |
+-----------------+-----------------+--------------+
|       351       |       196       |   pending    |
+-----------------+-----------------+--------------+
|       352       |       197       |   pending    |
+-----------------+-----------------+--------------+
|       353       |       197       |   pending    |
+-----------------+-----------------+--------------+
|       354       |       198       |   prepared   |
+-----------------+-----------------+--------------+
|       355       |       198       |   prepared   |
+-----------------+-----------------+--------------+

What I want is to select all the customer_number whose customer_status is unpaid and order_status is unserved from table customer and the customer_number must not have its order_status preparing or prepared from customer_orders.

Basically I need to select all customer_number that is unpaid and unserve and must not have preparing or prepared.

Im sorry if I did not explain it well, any answers will help thanks.

Best Answer

SELECT *
FROM customer c
LEFT JOIN customer_orders co 
    ON c.customer_number = co.customer_number 
   AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
  AND c.order_status = 'unserved'
  AND co.cust_ord_number IS NULL
Related Question