mysql query - issue of duplicate results

Question

I've this query:

SELECT DISTINCT u.name_surname, u.avatar, u.location
FROM users AS u
JOIN connections AS c
  ON c.user_id = u.id
JOIN words_en AS w 
  ON w.id = c.word_id
LEFT JOIN friends AS f1
  ON f1.asker_user_id = :user_id
    AND f1.status = 1
    AND f1.asked_user_id = u.id
WHERE (w.word = :kwd
  OR u.location = :kwd
  OR u.name_surname = :kwd)
AND (u.privacy = 3 OR (u.privacy = 2 and f1.id IS NOT NULL))
AND c.deleted <> 1

I wanted to understand where match for kwd was coming from (which column),

so I added this in select:

/*,
    CONCAT_WS(",",
        CASE WHEN w.word = :kwd THEN "word" END,
        CASE WHEN u.location = :kwd THEN "location" END,
        CASE WHEN u.name_surname = :kwd THEN "name_surname" END) As matches*/

Problem after adding this is that whenever there is a double match, lets say both in location and name, than two results are displayed. How do i solve this?

valex · Accepted Answer · 2013-11-13 08:32:57Z

1

Try to use GROUP BY instead of DISTINCT and MAX for this additional field:

SELECT u.name_surname, u.avatar, u.location, 
       MAX(CONCAT_WS(",",
        CASE WHEN w.word = :kwd THEN "word" END,
        CASE WHEN u.location = :kwd THEN "location" END,
        CASE WHEN u.name_surname = :kwd THEN "name_surname" END)) As matches
FROM users AS u
JOIN connections AS c
  ON c.user_id = u.id
JOIN words_en AS w 
  ON w.id = c.word_id
LEFT JOIN friends AS f1
  ON f1.asker_user_id = :user_id
    AND f1.status = 1
    AND f1.asked_user_id = u.id
WHERE (w.word = :kwd
  OR u.location = :kwd
  OR u.name_surname = :kwd)
AND (u.privacy = 3 OR (u.privacy = 2 and f1.id IS NOT NULL))
AND c.deleted <> 1

GROUP BY u.name_surname, u.avatar, u.location

answered Nov 13, 2013 at 8:32

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user796443 Over a year ago

1 issue I'm having, and maybe you know how to solve, if user has no 0 entry in connections table, than select returns nothing, even if match is found in location for example :S

Collectives™ on Stack Overflow

mysql query - issue of duplicate results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related