0

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.

  • It means that if all of the orders of the customer where completed or prepared – ropenrom24 Nov 21 '18 at 16:22
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.