How to do this kind of query?

Question

A user can have suspensions. I want to select a user and his suspensions, but I only want the suspensions where the suspensions.suspended_date > now(). I want to return the user regardless.

It sounds like a left join, but:

SELECT *
FROM users
LEFT JOIN suspensions ON suspensions.user_id=users.id
WHERE suspensions.suspended_date > now()

Would work fine if they DO have suspensions, but would cause trouble if they don't have any, because the where clause would always fail.

How can I write this with one query?

Answer 1

SELECT *
FROM users
LEFT JOIN suspensions ON suspensions.user_id=users.id
WHERE suspensions.suspended_date > now() or suspensions.suspended_date is null

That should do it.

Answer 2

you could try moving the filter from the WHERE to the JOIN statement

SELECT *
FROM   users 
       LEFT JOIN suspensions 
       ON suspensions.user_id=users.id AND suspensions.suspended_date > now()

Answer 3

would this work?

SELECT *
FROM users
LEFT JOIN suspensions ON suspensions.user_id=users.id
WHERE suspensions.suspended_date > now() or suspensions.user is null