CODEWITHSUNDEEP
mysqlselectdistinct
Saeesh Tendulkar
Saeesh Tendulkar

Reputation: 703

Mysql SELECT only unique values in one column when left joined with another table

This is the query:

SELECT a.id, a.userName,if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
       FROM tbl_users AS a 
       LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
       LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
  ORDER BY relation

This returns the output as follows:

+----+--------------+-------------+
| ID | USERNAME     | RELATION    | 
+----+--------------+-------------+
|  1 |          ray |           C | 
+----+--------------+-------------+
|  2 |         john |           I | 
+----+--------------+-------------+
|  1 |         ray  |           N | 
+----+--------------+-------------+

I need to remove the third row from the select query by checking if possible that id is duplicate. The priority is as follows:

C -> I -> N. So since there is already a "ray" with a C, I dont want it again with an I or N.

I tried adding distinct(a.id) but it doesn't work. How do I do this?

Why doesn't DISTINCT work for this?

Upvotes: 0

Views: 295

Answers (2)

fancyPants
fancyPants

Reputation: 51888

There are multiple ways to get the group-wise maximum/minimum as you can see in this manual page.

The best one suited for you is the first one, if the order of the rows can not be defined by alphabetic order.

In this case, given if the desired order were z-a-m (see Rams' comment) you'd need the FIELD() function.

So your answer is

SELECT 
a.id, 
a.userName,
if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
FROM tbl_users a
LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
WHERE 
if(o.userId=1,'C',if(i.userId=1,'I','N')) = (
    SELECT 
    if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
    FROM tbl_users aa
    LEFT JOIN tbl_contacts AS o ON aa.id = o.contactId 
    LEFT JOIN tbl_invites AS i ON aa.id = i.invitedId 
    WHERE aa.id = a.id AND aa.userName = a.userName
    ORDER BY FIELD(relation, 'N', 'I', 'C') DESC 
    LIMIT 1
)

Note, you can also do it like ORDER BY FIELD(relation, 'C', 'I', 'N') to have it more readable / intuitive. I turned it the other way round, because if you'd have the possibility of having a 'X' in the relation, the FIELD() function would have returned 0 because X is not specified as a parameter. Therefore it would be sorted before 'C'. By sorting descending and turning the order of the parameters around this can not happen.

Upvotes: 1

Leo Aso
Leo Aso

Reputation: 12473

From the specs you gave, all you have to do is group by ID and username, then pick the lowest value of relation you can find (since C < I < N)

SELECT a.id, a.userName, MIN(if(o.userId=1,'C',if(i.userId=1,'I','N'))) AS relation 
    FROM tbl_users AS a 
    LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
    LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
GROUP BY a.id, a.username

Upvotes: 1

Related Questions