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

Question

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?

What do you want to do with the relation? There's two different relations for `1 ray C/N` C and N. Are you sure you want to throw that 3rd row away? — Dan D, Sep 25 '17 at 12:26
yes I'm sure. The priority is as follows C -> I -> N. So if there is a C with an userID then I dont want a N with that same userID — Saeesh Tendulkar, Sep 25 '17 at 12:30
research MySQL [GROUP BY clause](https://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by). — Martin, Sep 25 '17 at 12:43
`Why doesn't DISTINCT work for this?` -- err, where have you used DISTINCT? — Martin, Sep 25 '17 at 12:47
@SaeeshTendulkar Regarding your comment in your other question (tip: you can direct messages with @ and the name. Then I would've noticed it). It is a duplicate. The fact that you have to join a second or a third table, doesn't make this any different. It still works the same way. In your case you simply have to use MIN() instead of MAX() and before you do the subquery as in the accepted answer you join your tables. Simple as that. — fancyPants, Sep 25 '17 at 12:49
I tried adding in "group by a.id" too but it cuts down some rows...I dont know how — Saeesh Tendulkar, Sep 25 '17 at 12:50
I cant use MIN() or MAX() if it all depends on the letter .Whether it's a "C" or "N". And I tried all the duplicate question solutions...NOT WORK — Saeesh Tendulkar, Sep 25 '17 at 12:51
@SaeeshTendulkar C comes before N in the alphabet, right? Then MIN() works. — fancyPants, Sep 25 '17 at 12:52
@fancyPants I think it not exact duplicate of what you are tagged — Rams, Sep 25 '17 at 12:52
I cant form my query like it is done in the duplicated question. — Saeesh Tendulkar, Sep 25 '17 at 12:53
@fancyPants if the order is z - a - m then ?? it should not be replaced with min or max function right... there should be general solution — Rams, Sep 25 '17 at 12:54
@rams. It's not indicated in the question, but it's a good point you made. Reopened and will answer the question. — fancyPants, Sep 25 '17 at 12:58

score 1 · Accepted Answer · answered Sep 25 '17 at 13:17

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

score 1 · Answer 2 · answered Sep 25 '17 at 13:21

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.

Won't this query take a lot of time? It has "4 left joins"!! — Saeesh Tendulkar, Sep 26 '17 at 04:16
This isn't necessarily bad. It depends on if you have created your tables with proper indexes. If you have this query won't make any trouble. — fancyPants, Sep 26 '17 at 07:33

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

2 Answers2