CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 3486

Combining common values in mySQL query

I have the following query:

"SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail 
FROM `emails` WHERE ((`emails`.`from` = '".$loggedMember->id."') 
OR (`emails`.`to` = '".$loggedMember->id."')) 
AND (`emails`.`to` != 0) ORDER BY `id` DESC LIMIT 4"

And get the following result:

to    fromEmail
887   1923
1923  887
1637  887
370   887

Question: How can I avoid repeated values in the sense of the first two, even though they are the other way around they are still considered to be repeated "887 1923" and "1923 887".

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

To eliminate repeated values, you need to put the emails in a canonical order:

SELECT DISTINCT
       (case when `emails`.`to` < `emails`.`from` then `emails`.`to`
             else `emails`.`from`
        end) as email1,
       (case when `emails`.`to` >= `emails`.`from` then `emails`.`to`
             else `emails`.`from`
        end) as email2
FROM `emails`
WHERE ((`emails`.`from` = '".$loggedMember->id."')  OR (`emails`.`to` = '".$loggedMember->id."')) AND (`emails`.`to` != 0)
ORDER BY `id` DESC LIMIT 4

If the fields could be NULL, then the logic would be a bit more complicated, but it would still be the same idea: to order the values in a particular order, before doing the distinct.

Upvotes: 2

Related Questions