CODEWITHSUNDEEP
mysqldaterangebetween
Adrián Granado Blanco
Adrián Granado Blanco

Reputation: 27

MySQL - Get records from INNER JOIN not between dates

I have two tables

Accounts:

+------------+--------+
| accountsid | name   |
+------------+--------+
|          1 | Bob    |
|          2 | Rachel |
|          3 | Mark   |
+------------+--------+

Sales Orders

+--------------+------------+------------+--------+
| salesorderid | accountsid | so_date    | amount |
+--------------+------------+------------+--------+
|            1 |          1 | 2015-12-16 |     50 |
|            2 |          1 | 2016-01-13 |     20 |
|            3 |          2 | 2015-12-14 |     10 |
|            4 |          3 | 2016-02-14 |     35 |
+--------------+------------+------------+--------+

As you can see, is a 1-N relation where Accounts has many Salesorders and Salesorder has 1 Account.

I need to retrieve "old" Accounts where are not active anymore. For example, If some Account dont have Salesorder in 2016 is an inactive Account.

So, in this example the result will be ONLY Rachel.

How can i retrieve this? I think its the "opposite" of between but I cant figure how to do it...

Thanks.

PS. Despite the title I can get this without INNER JOIN.

Upvotes: 1

Views: 65

Answers (3)

eggyal
eggyal

Reputation: 125855

You're looking to effect an anti-join, for which there are three possibilities in MySQL:

  1. Using NOT IN:

    SELECT a.*
FROM   Accounts a
WHERE  a.accountsid NOT IN (
         SELECT so.accountsid
         FROM   `Sales Orders` so
         WHERE  so.so_date >= '2016-01-01'
       )

  2. Using NOT EXISTS:

    SELECT a.*
FROM   Accounts a
WHERE  NOT EXISTS (
         SELECT *
         FROM   `Sales Orders` so
         WHERE  so.accountsid = a.accountsid
            AND so.so_date >= '2016-01-01'
       )

  3. Using an outer JOIN:

    SELECT a.*
FROM   Accounts a LEFT JOIN `Sales Orders` so
    ON so.accountsid = a.accountsid
   AND so.so_date >= '2016-01-01'
WHERE  so.accountsid IS NULL

Upvotes: 1

drosam
drosam

Reputation: 2896

What you want is to get the ids that didn´t make any order, so get the ids that made some order and the rest of them are the ones that didn´t make orders.

It should be something like this SELECT * FROM Accounts WHERE accountsid NOT IN (SELECT accountsid FROM Sales Orders WHERE so_date > your_date)

Upvotes: 0

gilg
gilg

Reputation: 73

why do you need to use only inner join? inner join is for cases you have data matching on two tables but in this case you don't you need to be using a subquery with either "not in" or "not exists"

Upvotes: 0

Related Questions