Issue
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.
Solution
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 = ?
Answered By – philipxy
Answer Checked By – Gilberto Lyons (BugsFixing Admin)