Pull multiple columns when an alias is used

Question

I need to pull two columns from an SQL table, however I need to use one as an alias due to the DISTINCT(). I cannot find much in the docs for this situation, so I have turned here.

This works (without the second column):

SELECT distinct(message_recips.userid) as userid 
FROM message_recips WHERE messageid = ?

However this doesn't:

SELECT distinct(message_recips.userid) as userid, users.email 
FROM message_recips 
INNER JOIN users ON users.email = message_recips.userid 
WHERE messageid = ?

Why does adding the second column (users.email) cause this to not work? I am simply trying to get the email addresses of the users who are recipients of the messages. The first query gets the message recipients perfectly, and I am pretty confident that is the correct format to get the email addresses.

The query returns no results, but not an error.

Answer 1

The second query is asking for the userids and emails from all possible rows that can be made from a row from each table where also the users.email = message_recips.userid.

Since you get get no rows back, you have no user emails that are equal to a message_recips userid.

(Not surprising.)

You probably want:

SELECT distinct(message_recips.userid) as userid, users.email 
FROM message_recips 
INNER JOIN users ON users.userid = message_recips.userid 
WHERE messageid = ?