How to relate the same table without conflict?

Question

How to relate the same table without conflict?

Navigation

#1 by (3 votes)

0

I have a user table, and a tanned table. The tanned table has: usuario_curtido and usuario_curtiu .

When I run

select u.usuario_nome from usuarios u
JOIN te_curtidas_usuarios c
ON c.curtida_usuario_curtido = u.usuario_id
ORDER BY c.curtida_data LIMIT 15

Users are in conflict - I always get the same user.

Answer:

array(7) {
  [0]=>
  array(1) {
    ["usuario_nome"]=>
    string(11) "Maria Joana"
  }
  [1]=>
  array(1) {
    ["usuario_nome"]=>
    string(16) "Daniel Bonifacio"
  }
  [2]=>
  array(1) {
    ["usuario_nome"]=>
    string(16) "Daniel Bonifacio"
  }
  [3]=>
  array(1) {
    ["usuario_nome"]=>
    string(16) "Daniel Bonifacio"
  }
  [4]=>
  array(1) {
    ["usuario_nome"]=>
    string(16) "Daniel Bonifacio"
  }
  [5]=>
  array(1) {
    ["usuario_nome"]=>
    string(16) "Daniel Bonifacio"
  }
  [6]=>
  array(1) {
    ["usuario_nome"]=>
    string(15) "Daniel Oliveira"
  }
}

Table:

mysql

asked by anonymous 14.03.2018 / 05:31

1 answer

Problem with windows, does not recognize another RAM [closed] Remove "." "/" and "-" from varchar in select

score 3 · Accepted Answer

Update

From what I understand you are not relating the same table, you are listing the usuarios table with te_curtidas_usuarios through JOIN. In this case they will return all occurrences of the second table curtida_usuario_curtido where curtida_usuario_curtido is equal to usuario_id , repeating the usuario_nome names that are in the first usuarios table.

To prevent the same user from being repeated, you must group the result of the query with the id of the user using GROUP BY:

select u.usuario_nome from usuarios u
JOIN te_curtidas_usuarios c
ON c.curtida_usuario_curtido = u.usuario_id
GROUP BY u.usuario_id
ORDER BY c.curtida_data LIMIT 15