Comparison of Two MYSQL TIMESTAMP Columns not providing correct results

Question

I have 2 tables client and followup. I need to select based on following conditions.

If subscriber_id in table client t1 NOT IN followup table t2 and t1.paid_amount = 0 AND t1.update_balance > 200

OR

If subscriber_id IS IN table followup t2 and and t1.paid_amount = 0 AND t1.update_balance > 200 AND ALSO if t1.update_time GREATER THAN t2.update_time. Both the update_time in both tables are in MYSQL TIMESTAMP Format.

My current query is as follows:

SELECT t1.id,t1.subscriber_id,t1.paid_amount,t1.update_balance
FROM client AS t1
LEFT OUTER JOIN followup AS t2 ON t1.subscriber_id = t2.subscriber_id
WHERE (t2.subscriber_id IS NULL AND t1.paid_amount = 0 AND t1.update_balance > 200) OR (t2.subscriber_id IS NOT NULL AND t1.paid_amount = 0 AND t1.update_balance > 200 AND 't1.update_time'> 't2.update_time')

But the data is not selected if t1.update_time > THAN t2.update_time..

Client Table:
-------------------------------------------------------------------
id   subscriber_id  paid_amount    update_balance  update_time
--------------------------------------------------------------------
1     AB4567            0              500      2020-07-12 18:22:24
2.    AB4568            0              300      2020-07-15 17:22:24
--------------------------------------------------------------------

Followup Table
------------------------------------------------------------------
id   subscriber_id  feedback  update_time
-------------------------------------------------------------------
1.   AB4567          paid      2020-07-12 17:22:24 
-------------------------------------------------------------------

1. If subscriber_id NOT IN table 2 - Display ROW
2. If subscriber_id IN table 2 - Display if t1.update_time > t2.update_time

Requesting Help from Mysql Experts..

Answer 1

I'm not sure this is the whole problem, but

UNIX_TIMESTAMP('t1.update_time'> 't2.update_time')

should be

t1.update_time > t2.update_time

First, there's no need to use UNIX_TIMESTAMP when comparing, you can just compare the dates directly.

Second, you shouldn't quote the column names. That makes them string literals. See When to use single quotes, double quotes, and backticks in MySQL.

Third, if you do use UNIX_TIMESTAMP, you need to call it separately on each column, e.g.

UNIX_TIMESTAMP(t1.update_time) > UNIX_TIMESTAMP(t2.update_time)

Your code was comparing the strings, which evaluates to TRUE, which is 1, so it was evaluating UNIX_TIMESTAMP(1)

To make the whole query work, split it up into two queries that you combine with UNION.

SELECT t1.id,t1.subscriber_id,t1.paid_amount,t1.update_balance
FROM client AS t1
LEFT OUTER JOIN followup AS t2 ON t1.subscriber_id = t2.subscriber_id
WHERE t1.paid_amount = 0 AND t1.update_balance > 200
AND t2.subscriber_id IS NULL

UNION 

SELECT t1.id,t1.subscriber_id,t1.paid_amount,t1.update_balance
FROM client AS t1
INNER JOIN followup AS t2 ON t1.subscriber_id = t2.subscriber_id AND t1.update_time > t2.update_time
WHERE t1.paid_amount = 0 AND t1.update_balance > 200

DEMO