CODEWITHSUNDEEP
mysqlsql
ShowMeTellMe
ShowMeTellMe

Reputation: 57

LEFT Join query issues with

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())

Upvotes: 5

Views: 80

Answers (4)

Peter Å
Peter Å

Reputation: 1319

I think what you are after is the left outer join to return all users and not only the one that have updates today. That should be something like this:

SELECT users.userid, updates.lastupdated
  FROM users
  LEFT OUTER JOIN (
    SELECT * FROM updates WHERE DATE(lastupdated) = DATE(NOW())
    ) updates ON users.userid = updates.userid

Upvotes: 0

Vinit Prajapati
Vinit Prajapati

Reputation: 1613

Try this

   SELECT users.userId, case when  DATE(LastUpdated) = DATE(NOW())
 updates.status else null end as status
     FROM users 
LEFT JOIN updates ON users.userId=updates.userIdd

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101594

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).

Upvotes: 2

lc.
lc.

Reputation: 116438

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())

Upvotes: 5

Related Questions