I'm having some issues getting the following query to work - in all cases I only seem to get a small subset of users back rather than the entire list.
I have the following two tables:
Users:
- UserId
- email
Updates:
- UserId
- Status
- LastUpdated
What I want to be returned is a list of all users (from Users), their status if it was updated today (based on LastUpdated field) otherwise BLANK or NULL if it wasn't updated today.
I've got as far as this:
SELECT users.userid,
updates.status
FROM users
LEFT JOIN updates
ON users.userid = updates.userid
WHERE Date(lastupdated) = Date(Now())
You can use a CASE
statement to only get updates.status
if the date is today:
SELECT users.userId,
CASE WHEN DATE(LastUpdated) = DATE(NOW()) THEN updates.status ELSE NULL END AS status
FROM users
LEFT JOIN updates ON users.userId=updates.userId
Or, better yet, if you don't need anything else from the updates
row just do:
SELECT users.userId, updates.status
FROM users
LEFT JOIN updates ON users.userId=updates.userId
AND DATE(LastUpdated) = DATE(NOW())
SELECT users.userId, updates.status
FROM users
LEFT JOIN updates
ON updates.userId = users.userId
AND DATE(updates.LastUpdated) = DATE(NOW())
Put your condition within the join, otherwise it's forcing every join to be interrogated. Alternatively, you can check for LastUpdated
to be NULL
I believe (since the LEFT join will only include located information).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With