Search code examples
mysqlwebranking

SQL Query returning duplicate values

I am developing a ranking system for an online game webite and am having trouble with the query that returns duplicate values.

The query:

SELECT * FROM `personajes`,`ranking_pvp` ORDER BY `victorias` DESC LIMIT 30

The result: https://i.sstatic.net/2A3wJ.jpg

The duplicated values​:

`nombre` , `victorias` , `derrotas`

The 'personajes' table:

  • id INT(11) NOT NULL
  • nombre VARCHAR(30) NOT NULL
  • sexo TINYINT(2) NOT NULL
  • clase SMALLINT(3) NOT NULL
  • color1 INT(11) NOT NULL
  • color2 INT(11) NOT NULL
  • color3 INT(11) NOT NULL
  • kamas BIGINT(32) NOT NULL
  • puntosHechizo INT(11) NOT NULL
  • capital INT(11) NOT NULL
  • energia INT(11) NOT NULL DEFAULT '10000'
  • nivel INT(11) NOT NULL
  • xp BIGINT(32) NOT NULL DEFAULT '0'
  • talla INT(11) NOT NULL
  • gfx INT(11) NOT NULL,
  • alineacion INT(11) NOT NULL DEFAULT '0',
  • honor INT(11) NOT NULL DEFAULT '0',
  • deshonor INT(11) NOT NULL DEFAULT '0',
  • nivelAlin INT(11) NOT NULL DEFAULT '0' COMMENT 'Nivel alineamiento',
  • cuenta INT(11) NOT NULL,
  • vitalidad INT(11) NOT NULL DEFAULT '0',
  • fuerza INT(11) NOT NULL DEFAULT '0',
  • sabiduria INT(11) NOT NULL DEFAULT '0',
  • inteligencia INT(11) NOT NULL DEFAULT '0',
  • suerte INT(11) NOT NULL DEFAULT '0',
  • agilidad INT(11) NOT NULL DEFAULT '0',
  • mostrarAmigos TINYINT(4) NOT NULL DEFAULT '1',
  • mostrarAlineacion INT(11) NOT NULL DEFAULT '0',
  • canal VARCHAR(15) NOT NULL DEFAULT '*#%!pi$:?',
  • mapa INT(11) NOT NULL DEFAULT '8479',
  • celda INT(11) NOT NULL,
  • porcVida INT(11) NOT NULL DEFAULT '100',
  • hechizos TEXT NOT NULL,
  • objetos TEXT NOT NULL,
  • posSalvada VARCHAR(20) NOT NULL DEFAULT '8570,314',
  • zaaps VARCHAR(250) NOT NULL DEFAULT '',
  • oficios VARCHAR(300) NOT NULL DEFAULT '',
  • xpMontura TINYINT(2) NOT NULL DEFAULT '0',
  • montura INT(11) NOT NULL DEFAULT '-1',
  • titulo VARCHAR(255) NOT NULL DEFAULT '0',
  • esposo INT(11) NOT NULL DEFAULT '0',
  • tienda TEXT NOT NULL,
  • mercante INT(11) NOT NULL DEFAULT '0',
  • sFuerza INT(11) NOT NULL DEFAULT '0',
  • sInteligencia INT(11) NOT NULL DEFAULT '0',
  • sAgilidad INT(11) NOT NULL DEFAULT '0',
  • sSuerte INT(11) NOT NULL DEFAULT '0',
  • sVitalidad INT(11) NOT NULL DEFAULT '0',
  • sSabiduria INT(11) NOT NULL DEFAULT '0',
  • restriccionesA INT(11) NOT NULL DEFAULT '8192',
  • restriccionesB INT(11) NOT NULL DEFAULT '0',
  • encarnacion INT(11) NOT NULL DEFAULT '-1',
  • emotes INT(11) NOT NULL DEFAULT '0',
  • ornamento TINYINT(2) NOT NULL DEFAULT '0',
  • ornamentos VARCHAR(255) NOT NULL,
  • misiones TEXT NOT NULL,
  • coleccion TEXT NOT NULL,
  • resets SMALLINT(3) NOT NULL DEFAULT '0',
  • almanax TEXT NOT NULL,
  • ultimoNivel INT(11) NOT NULL DEFAULT '0',
  • setsRapidos TEXT NOT NULL COLLATE 'latin1_spanish_ci'
  • PRIMARY KEY (id)

The 'ranking_pvp' table:

  • id INT(11) NOT NULL,
  • nombre VARCHAR(255) NOT NULL,
  • victorias INT(11) NOT NULL,
  • derrotas INT(11) NOT NULL,
  • nivelAlineacion INT(11) NOT NULL,
  • PRIMARY KEY (id)

Someone could increase my query to remove the duplicate values?

Waiting and Thx ;)

Solution

  • With the info you just added as a comment:

    SELECT *
FROM personajes p
JOIN ranking_pvp rp ON rp.nombre = p.nombre
ORDER BY `victorias` DESC
LIMIT 30