LibreOffice HSQLDB WHERE clause with LEFT JOIN and MAX?-CodePudding

I'm running macOS 11.6,LibreOffice 7.2.2.2,HSQLDB (my understanding is this is v.1.8, but don't know how to verify)

I'm a newbie to SQL, and I'm trying to write a DB to maintain a club membership roster. I'm trying to find everyone in the DB to whom renewal letters should be sent. The quirk is, if a person has never paid in the past, they should be sent a renewal letter. Old members who haven't renewed recently don't get a renewal, and obviously, each individual should only get one letter. I've created a toy example to display the problem I'm having...

Members table:
Key (Integer, Primary key, Autoincrement)
Name (Varchar)
 ----- ---------- 
| Key | Name     |
 ----- ---------- 
|  0  | Abby     |
|  1  | Bob      |
|  2  | Dave     |
|  3  | Ellen    |
 ----- ---------- 

Payments table:
Key (Integer, Primary Key, autoincrement)
MemberKey (Integer, foreign key to Member table)
Payment Date (Date)
 ----- ----------- -------------- 
| Key | MemberKey | Payment Date |
 ----- ----------- -------------- 
|  0  |     0     | 2020-05-23   |
|  1  |     0     | 2021-06-12   |
|  2  |     1     | 2016-05-28   |
|  3  |     2     | 2020-07-02   |
 ----- ----------- --------------

The only way I've found to include everyone is with a LEFT JOIN. The only way I've found to pick the most recent payment is with MAX. The following query produces a list of everyone's most recent payments, including people who've never paid:

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
GROUP BY "Members"."Key", "Members"."Name"

It returns the result below, which includes all members only once (Abby has 2 payments but only appears once with the most recent payment). Unfortunately it still includes people like Bob who've been out of the club so long that we don't want to send them a renewal notice.

 ----- ---------- -------------- 
| Key | Name     | Last Payment |
 ----- ---------- -------------- 
|  0  | Abby     | 2021-06-12   |
|  1  | Bob      | 2016-05-28   |
|  2  | Dave     | 2020-07-02   |
|  3  | Ellen    |              |
 ----- ---------- --------------

Where I hit a wall is when I try to perform any kind of conditional operation on the Last Payment, to determine whether it's recent enough to include in the list of renewal notices. For instance, in HSQLDB, the query below returns the error, "The data content could not be loaded. Not a condition." The only change in this query from the 1st one is the addition of the WHERE clause.

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
WHERE "Last Payment" >= '2020-01-01'
GROUP BY "Members"."Key", "Members"."Name"

The desired output should look like this:

 ----- ---------- -------------- 
| Key | Name     | Last Payment |
 ----- ---------- -------------- 
|  0  | Abby     | 2021-06-12   |
|  2  | Dave     | 2020-07-02   |
|  3  | Ellen    |              |
 ----- ---------- --------------

I've been digging around the web trying anything that looks relevant. I've tried "HAVING" clauses--I can make them work with a COUNT(*) function, but I can't make them work with a MAX(*) function. I've tried using my 1st query as a subquery, and applying the WHERE clause on "Last Payment" in the main query. I've tried solutions people say work in MySQL, but I can't get them to work in HSQLDB. I tried using the 1st query as a View, and writing a query against the View. I've tried a dozen other things I don't even remember. Everything past the 1st query above throws an error. I wanted to include my toy DB, but can't find a way to attach it to the post.

Can anyone help please?

CodePudding user response：

This worked for me.

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM {oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey"
    WHERE "Payments"."Payment Date" >= '2020-01-01'
    OR "Payments"."Payment Date" IS NULL}
GROUP BY "Members"."Key", "Members"."Name"

Result:

This works as well.

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
WHERE "Payments"."Payment Date" >= '2020-01-01'
    OR "Payments"."Payment Date" IS NULL
GROUP BY "Members"."Key", "Members"."Name"

Perhaps the problem you were having is that "Last Payment" is only a column title and not the actual name of any column.