CODEWITHSUNDEEP
mysqlsql
Chad
Chad

Reputation: 1818

How do I find records in one table that do exist in another but based on a date?

I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the only dealer in this example to not have an order in 2015 yet.

SELECT d.`name` 
FROM z_dealer d 
LEFT JOIN z_order o ON (d.promo_code = o.promo_code) 
WHERE o.promo_code IS NULL
AND o.date_ordered > '2015-01-01 00:00:00'

Here is the table data...

mysql> Select * from z_order;
+----+-------+------------+---------------------+
| id | total | promo_code | date_ordered        |
+----+-------+------------+---------------------+
| 1  | 10    | holiday    | 2014-06-22 09:06:50 |
| 2  | 20    | special    | 2015-06-22 09:07:04 |
| 3  | 15    | holiday    | 2015-03-01 09:07:23 |
| 4  | 45    | special    | 2014-09-03 09:07:33 |
| 5  | 16    | laborday   | 2014-06-22 09:09:01 |
+----+-------+------------+---------------------+



mysql> select * from z_dealer;
+----+------+------------+
| id | name | promo_code |
+----+------+------------+
| 1  | John | holiday    |
| 2  | Suzy | special    |
| 3  | Bob  | laborday   |
+----+------+------------+

Upvotes: 0

Views: 47

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You need to tell the query that you want to compare timestamps, not strings, and you need to include all the JOIN conditions in the ON clause of the JOIN, not in the WHERE clause. Change the query to

SELECT d.`name` 
  FROM Table2 d 
  LEFT JOIN Table1 o ON (d.promo_code = o.promo_code AND
                         o.date_ordered > TIMESTAMP '2015-01-01 00:00:00') 
  WHERE o.promo_code IS NULL

Best of luck.

Upvotes: 0

Umair M
Umair M

Reputation: 10720

    SELECT d.`name` 
    FROM z_dealer d 
    LEFT JOIN z_order o ON (d.promo_code = o.promo_code) 
    WHERE o.promo_code IS NULL
    AND o.date_ordered > 2015-01-01 00:00:00

Date value between ' ' is read as a string value not a DateTime. So above code works. And if you want your result "Bob" remove Where clause from query as it is considered as predicate which may alter your results.

P.S you should not use hyphens in Date Value as its representing cultural format. Have a look at This for good practices.

Upvotes: 0

PaulF
PaulF

Reputation: 6773

select d.`name`
from z_dealer d
where
  (select count(*)
   from z_order o
   WHERE o.promo_code = d.promo_code
   AND o.date_ordered > '2015-01-01') = 0

Upvotes: 1

Related Questions