MySQL query error when using NOT EXISTS()

Question

I am trying to run a MySQL query utilising the NOT EXISTS MySQL function on a legacy system (running MySQL 4.x), below is a simple query that return a few columns from two tables (with one INNER JOIN).

NB customerId and accountId are the same values.. it is a really old system :(

SELECT d.customerId, d.customerName, a.status
    FROM DetailedDebtorsData AS d 
    INNER JOIN accounts AS a ON a.account_uid = d.customerId
    ORDER BY d.date ASC;

+---------------+----------------------+--------+
| customerId    | customerName         | status |
+---------------+----------------------+--------+
|        145060 | Alan Smith           | active |
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

I have another table that I will be using as an 'exceptions' table, in essence if the customerId (with its related account_id column in the exclusions) is found in the account_payment_terms exceptions table then I DON'T want return the related row from the DetailedDebtors table above.

account_payment_terms table (for exclusions)

mysql> SELECT account_id, created_date FROM account_payment_terms;
+------------+---------------------+
| account_id | created_date        |
+------------+---------------------+
|     145060 | 2023-11-20 13:23:03 |
+------------+---------------------+

As the account_id 145060 exists in the accountPaymentTerms table, then I do not want to return the associated row (that has the same 145060 id) in the DetailedDebtors table to be returned.

Here is the query I am trying to execute with NOT EXISTS()

SELECT d.customerId, d.customerName, a.status
    FROM DetailedDebtorsData AS d 
    INNER JOIN accounts AS a ON a.account_uid = d.customerId
    WHERE NOT EXISTS (
        SELECT 1
        FROM account_payment_terms AS apt
        WHERE apt.account_id = d.customerId
    )        
    ORDER BY d.date ASC;

This errors and gives me the following error, but I want the WHERE NOT EXISTS to simply only return the one row , the row that IS NOT in the account_payment_terms table.

Error returned in MySQL

ERROR 1064 (HY000): You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (
        SELECT 1
        FROM account_payment_terms AS

Expected Outcome (return 1 row)

+---------------+----------------------+--------+
| accountId     | customerName         | status |
+---------------+----------------------+--------+
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

Actual Outcome (returns 2 row)

+---------------+----------------------+--------+
| accountId     | customerName         | status |
+---------------+----------------------+--------+
|        145060 | Alan Smith           | active |
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

What am I doing wrong?

Answer 1

Here's a solution to do an exclusion join à la MySQL 4.0:

SELECT d.customerId, d.customerName, a.status
FROM DetailedDebtorsData AS d 
INNER JOIN accounts AS a ON a.account_uid = d.customerId
LEFT OUTER JOIN account_payment_terms AS apt
  ON apt.account_id = d.customerId
WHERE apt.account_id IS NULL
ORDER BY d.date ASC;