CODEWITHSUNDEEP
mysql
Buster
Buster

Reputation: 697

MySQL select query if no records occurred in the last 12 months

I'm trying to create a Select query which will only show rows older than 12 months, if there's no record for the same column in the last twelve months. For example, my table looks like the following:

id supid lastqual
1 1 2019-01-08
2 1 2020-08-03
3 7 2019-09-14

The closest I've gotten is the following query

SELECT
  Suppliers.id,
  Suppliers.company,
  Qual.id,
  Qual.lastqual
FROM 
  Suppliers
  LEFT JOIN Qual ON Suppliers.id = Qual.supid
WHERE Qual.lastqual < DATE_SUB(now(), INTERVAL 12 MONTH)

which returns row 1 and 3. However what I'm trying to get is a statement that will exclude row 1 from the Qual table, since there's also a record (row 2) which occurred inside of the last 12 months.

What WHERE statement can I include to accomplish this?

Upvotes: 1

Views: 433

Answers (1)

Nick
Nick

Reputation: 147166

You can use a NOT EXISTS clause to check that no record exists in Qual for the same supplier id with a date in the last twelve months:

SELECT
  Suppliers.id,
  Suppliers.company,
  Qual.id,
  Qual.lastqual
FROM 
  Suppliers
  LEFT JOIN Qual ON Suppliers.id = Qual.supid
WHERE Qual.lastqual < DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
  AND NOT EXISTS (
    SELECT * 
    FROM Qual 
    WHERE supid = Suppliers.id AND Qual.lastqual >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) 
)

Note that for comparing dates it's probably more appropriate to use CURDATE() (which just returns the current date) than NOW() (which also returns the current time as part of the value).

Demo on db-fiddle

Upvotes: 1

Related Questions