Is there a way to write this as one query?

Question

Here's my situation. I have a user table and a password_reset table which references the user table.

I want to run a single query that will return some fields from a password_reset row that references their user row. However, I want to return just the user's email if they DO NOT have a row in password_reset. Here is what I have so far, which works in the event that they DO have a password_reset row, but not if they don't (in that case, it returns no rows at all).

SELECT  u.email, p.code, p.expires
FROM    users u, password_resets p
WHERE   u.username = :username
        AND u.id = p.user_id

How would I go about writing this? Can I even do it with just one query?

Answer 1

SELECT u.email, p.code, p.expires
FROM    users AS u
LEFT JOIN password_resets AS p USING (u.id = p.user_id)
WHERE   u.username = :username

Answer 2

SELECT Users.email
FROM   Users
    LEFT JOIN Password_resets ON Users.PrimaryKey = Password_resets.ForeignKey
WHERE Password_resets.ForeignKey IS NULL

Answer 3

SELECT  u.email, p.code, p.expires
  FROM    users u
LEFT JOIN password_resets p
ON
   u.id = p.user_id
WHERE  u.username = :username