Mysql SELECT eliminate rows duplicate values in one column (joining 2 tables)

Asked Sep 25 '17 at 10:53

Active Sep 27 '17 at 08:04

Viewed 46 times

This is a different question because I have joined 2 tables. The solutions for the duplicate question indicated does not work for me. 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. I tried adding distinct(a.id) but it doesn't work. How do I do this?

edited Sep 27 '17 at 08:04

Maraboc

10,550
3
37
48

asked Sep 25 '17 at 10:53

Saeesh Tendulkar

How do you decide which row you should remove- the first one (1/C) or the third (1/N)? – Maxim Zhukov Sep 25 '17 at 11:05
the third one I want to remove, giving preference in the order C,I,N – Saeesh Tendulkar Sep 25 '17 at 11:07
I have already sorted it on 'relation' (i.e. C or N). I just want to remove the duplicate row which is coming second. Any idea? – Saeesh Tendulkar Sep 25 '17 at 11:13
how is this a duplicate? that guy didnt actually join 2 tables he just wanted distinct values of a row in a "SINGLE" table.... fml – Saeesh Tendulkar Sep 25 '17 at 11:23

Mysql SELECT eliminate rows duplicate values in one column (joining 2 tables)

0 Answers0

Linked