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
-------------------------------------------------------------------
Requesting Help from Mysql Experts..
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