0

I am having trouble with this mysql query. I have a customer table and an orders table, linked by a customer ID. The orders table has a date field and I am trying to list the names of customers that do not have any orders for a particular month, July 2016. I was told a NOT IN might help but I'm not sure how to go about it. I looked for similar examples but they use IS NULL. I tried this but it did not like the NOT (not a valid input at this position:

SELECT customer.cust_name FROM customer LEFT JOIN ordertbl ON ordertbl.cust_id = customer.cust_id WHERE order_date like '2016-07%' not in ordertbl.order_date;

I then tried this but it returned no results:

SELECT customer.cust_name FROM customer LEFT JOIN ordertbl ON ordertbl.cust_id = customer.cust_id WHERE (SELECT COUNT(order_date like '2016-07%')) IS NULL;

I also found a similar example but couldn't get it, no results:

Select customer.cust_name From customer where ordertbl.cust_id Not in ( Select customer.cust_id From ordertbl Where ordertbl.order_date like '2016-07%');

I'm sure I'm going about this all wrong. I tried a few other examples but those didn't work either. Any help is appreciated.

user2328273
  • 868
  • 3
  • 12
  • 22
  • Still truggling? See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 01 '17 at 19:33

1 Answers1

0

Assuming that in the orders table, your customer ID reference is called CUSTOMER_ID, the query is: 

SELECT CUSTOMER.CUST_NAME
    FROM CUSTOMER
    WHERE CUST_ID IN
        (SELECT ORDERTBL.CUSTOMER_ID 
        FROM ORDERTBL
        WHERE DATE <> yourdate)
ollaw
  • 2,086
  • 1
  • 20
  • 33
  • Thanks, that seems to have worked as intended but I have a question about the date. I don't want a specific date but a month so I was using `LIKE '2016-07' instead of `<> yourdate. It seems to have worked but based on the fact that you used `<>` I would have expected to have to use `NOT LIKE` instead. Can you explain why that is not the case? – user2328273 Feb 01 '17 at 19:46
  • To use LIKE operator, you should convert the date to text. But it's easier instaed, writing : WHERE DATE >= '2017-01-01' AND DATE <= '2017-01-02' for range. – ollaw Feb 01 '17 at 19:55