CODEWITHSUNDEEP
mysqldistinct
Layke
Layke

Reputation: 53146

Get Distinct Values from Multiple Columns (Including a Join)

This one has stumped me.

A quick overview

I need to be able to get all email addresses of people who have participated in a message thread. People can add messages to this messageThread via a few different means, and because people can either be the sender or receive a message, the email addresses might be in different columns.

Here is an example table.

messagesTable

ID | threadKey |  fromEmail   |  toEmail    | sentBy   |   sentTo
1  | ASJHD2    | [email protected]  | [email protected] |  null    |   null
2  | ASJHD2    | [email protected]   |             |  null    |   null
3  | ASJHD2    |              |             |  55      |   88

I also have my Users table. 

   ID   | username  | email
   55   | Ike       | [email protected]
   88   | Sam       | [email protected]

Now is it possible to get all of the distinct emails that have participated in the message with a threadKey of ASJHD2. Keeping in mind that there would also need to be a join on the users table.

Upvotes: 0

Views: 517

Answers (3)

Layke
Layke

Reputation: 53146

Just for anyone interested. I used Max answer and added the Join also. This is the final query that I ended up with.

SELECT fromEmail as email FROM messages m WHERE fromEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT toEmail FROM messages m WHERE toEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentBy =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentTo =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"

Upvotes: 0

Andre
Andre

Reputation: 3181

What you need to do is essentially join to your users table but stating the condition to use either field for the join. Then you group the results based on the email addresses that you're looking for.

SELECT * FROM messagesTable AS m
JOIN Users AS u ON u.email = m.fromEmail OR u.email = m.toEmail
WHERE m.threadKey = 'ASJHD2'
GROUP BY u.email;

Upvotes: 0

Max
Max

Reputation: 842

First try to get just emails like

SELECT fromEmail
FROM messagesTable
WHERE fromEmail IS NOT NULL
UNION
SELECT toEmail
FROM messagesTable
WHERE toEmail IS NOT NULL

Upvotes: 2

Related Questions