0

I'm trying to get 3 random unique profiles with the same sex ID as the current user ID (orig.id_user = 6 in this example), and their respective reviews.

SELECT DISTINCT u.id_user, s.review
FROM user AS u
CROSS JOIN user AS orig ON orig.id_sex = u.id_sex
INNER JOIN user_review AS s ON s.id_user_to = u.id_user
WHERE orig.id_user = 6
ORDER BY RAND()
LIMIT 3

Somehow, the id_user column displays repeated values. Why?

UPDATE (assuming i have the id_sex value)

SELECT DISTINCT s.id_user_to, s.id_user_from, s.review
FROM user_review AS s
LEFT JOIN user AS u ON u.id_user = s.id_user_to
WHERE u.id_sex = 2
ORDER BY RAND()
LIMIT 20

But this is still returning repeated rows in the id_user_to column, they should be unique values because of the DISTINCT.

SOLUTION using GROUP BY

SELECT us.id_user_to, us.review
FROM user_review AS us
LEFT JOIN user AS u ON u.id_user = us.id_user_to
WHERE u.id_sex = 2
GROUP BY us.id_user_to
ORDER BY RAND()
LIMIT 3
Andres SK
  • 10,779
  • 25
  • 90
  • 152
  • Seems to work ok om [this SQL Fiddle](http://sqlfiddle.com/#!2/5f2a7/1), althougn it is also worth noting a `CROSS JOIN` with an `ON` clause is just an `INNER JOIN`, I'm surprised to see that this runs, I would expect a syntax error. Can you update the question/sql fiddle to reproduce the problem? – GarethD Oct 30 '13 at 23:39
  • 2
    `DISTINCT` is on the combination of u.id_user and s.review, so it is due to multiple reviews. – Glenn Oct 30 '13 at 23:45
  • I modified the original SQL sentence, assuming I have the id_sex value, but instead of throwing unique IDs in id_user_to, I get repeated values. Why? – Andres SK Oct 31 '13 at 22:39

0 Answers0