Reputation: 4028
I am trying to narrow down the resultset of a query, one way I can reduce this is by checking an audit table foo_status, when a particular column barStatus
has been set to "Live".
Foo table
+--------+----------+-----------+---------------------+
| id | status | barStatus | created |
+--------+----------+-----------+---------------------+
| 120534 | LIVE | LIVE | 13/02/2021 13:21:04 |
+--------+----------+-----------+---------------------+
Essentially the cardinality is (A Foo -- has many --> Foo Status Updates)
The table records when a foo states changes over time, e.g.
+------+-------------------+-------------------+-------+------------------+------------+--------+
|id |createdOn |modifiedOn |version|status |barStatus |foo_id |
+------+-------------------+-------------------+-------+------------------+------------+--------+
|213423|2018-09-02 03:21:56|2018-09-02 03:21:56|0 |ACTIVATED |UNSURE |120534 |
|213424|2018-09-02 03:30:22|2018-09-02 03:30:22|0 |REGISTERED |UNSURE |120534 |
|213425|2018-09-02 03:30:22|2018-09-02 03:30:22|0 |AWAITING_INTERVIEW|UNSURE |120534 |
|215396|2018-09-07 10:16:58|2018-09-07 10:16:58|0 |INVITED_TO_JOIN |UNSURE |120534 |
|215601|2018-09-07 21:09:13|2018-09-07 21:09:13|0 |INVITED_TO_JOIN |DBS_RECEIVED|120534 |
|215603|2018-09-07 21:17:12|2018-09-07 21:17:12|0 |READY_FOR_LIVE |DBS_RECEIVED|120534 |
|218345|2018-09-14 14:42:22|2018-09-14 14:42:22|0 |READY_FOR_LIVE |LIVE |120534 |
|218351|2021-02-14 14:45:21|2018-09-14 14:45:21|0 |LIVE |LIVE |120534 |
+------+-------------------+-------------------+-------+------------------+------------+--------+
I am wanting to restrict the results set for when a foo has become LIVE
for barStatus
within 5 days
My current query however returns no results
select count(foo.id)
from foos foo
left join foo_status_updates fsu on fsu.foo_id = foo.id
where fs.id = 1
and (foo.currentlyUnavailable IS FALSE)
and (foo.status in ('READY_FOR_LIVE', 'LIVE'))
and foo.barStatus = 'LIVE'
and (SELECT createdOn
FROM foo_status_updates fsu
WHERE foo_id = foo.id
AND barStatus = 'LIVE'
ORDER BY createdOn
LIMIT 1) > DATE_SUB(NOW(), INTERVAL 5 DAY)
In summary, I only want to get back Foo records which have had their barStatus
set to LIVE
recently (within 5 days)
Do I need to select the most recent column inside the JOIN
clause?
Upvotes: 1
Views: 41
Reputation: 431
Looks to me like the AND (SELECT
bit should just be
AND foo.id in (
SELECT foo_id
FROM foo_status_updates
WHERE createdOn > DATE_SUB(NOW(), INTERVAL 5 DAY)
AND barStatus = 'Live'
)
Upvotes: 1