Table 1:

Name    Address   Height    Weight   Date_Collected
X.        y         z.        p.       8/7/2024
G.        H.        I         J.       8/8/2024
Q         W.        E.        R.       8/9/2024

Table 2:

Name    Income     Tax    Date_Collected 
X.        y         z.      8/8/2024
A         B         C.      8/8/2024

Trying compare the rows between the two tables and get the names that appear only in table 1 and not in table 2. I am also trying to filter them out by the MAX date collected.

Here is what I have so far but I am getting a syntax error:

SELECT count(DISTINCT "Name") FROM table_1 WHERE table_1.Height NOT LIKE ANY CONCAT('%', {'I', 'E'} '%')

WHERE NOT EXISTS (

    SELECT DISTINCT UPPER("Name") FROM table_2 WHERE MAX(table_2."Date Collected") AND table_2."Income" = 'y' AND UPPER(table_2."Name") LIKE CONCAT('%', UPPER(table_1."Name"), '%')

)

aggregate functions are not allowed in "WHERE"

So I changed it to look like:

SELECT count(DISTINCT "Name") FROM table_1 WHERE table_1.Height NOT LIKE ANY CONCAT('%', {'I', 'E'} '%')

WHERE NOT EXISTS (

    SELECT DISTINCT UPPER("Name") FROM table_2 WHERE  table_2."Income" = 'y' AND UPPER(table_2."Name") LIKE CONCAT('%', UPPER(table_1."Name"), '%') AND HAVING MAX(table_2."Date Collected")

)

This gives me a syntax error. Removed the AND before HAVING, it says:

argument of HAVING must be type boolean, not type date

which I assume that there should be some condition after using HAVING. If I have to use having, what should the condition be if I want to get the MAX date? Can I get the MAX date without using HAVING?

This is in postgresql