how to select name with JOIN

[XenaNyx]

i create my pvp_ranking

CREATE TABLE `pvp_ranking` (
 `char_id` int(11) unsigned NOT NULL auto_increment,
 `kill_point` int(11) NOT NULL,
 `die_point` int(11) NOT NULL,
 PRIMARY KEY  (`char_id`)
) ENGINE=MyISAM  AUTO_INCREMENT=150007 ;

INSERT INTO `pvp_ranking` VALUES (150006, 0, 1);
INSERT INTO `pvp_ranking` VALUES (150000, 1, 15);

but i want to show ranking like

mes "[ Char Name ] Kill : xx points | Die : xx points | Total Kill - Die";

but i cant select name i try with

if ( query_sql( "SELECT `name`, `kill_point` FROM `pvp_ranking` JOIN `char` ON `pvp_ranking`.`char_id` = `char`.`char_id` WHERE `kill_point` ORDER BY `point` DESC LIMIT 10;", .@name$, .@kill_point ) ) 

it's work but

if ( query_sql( "SELECT `name`, `kill_point`-`die_point` FROM `pvp_ranking` JOIN `char` ON `pvp_ranking`.`char_id` = `char`.`char_id` WHERE `kill_point`-`die_point` ORDER BY `kill_point`-`die_point` DESC LIMIT 10;", .@name$, .@point ) ) 

it doesn't work i have this error

(23:36:29) SQL: DB error - Column 'die_point' in field list is ambiguous
(23:36:29) Debug: at trunk\src\map\script.c:13703 - SELECT `name`,`kill_point`,`die_point` FROM `pvp_ranking` JOIN `char` ON `pvp_ranking`.`char_id` = `char`.`char_id` WHERE `kill_point`-`die_point` ORDER BY `kill_point`-`die_point` DESC LIMIT 10;
(23:36:29) Debug: Source (NPC): rankmaster at morocc (136,111)

[Brian]

query_sql "SELECT `char`.`name`, `kill_point`, `die_point`, `kill_point`-`die_point` FROM `pvp_ranking` LEFT JOIN `char` ON pvp_ranking.char_id=`char`.char_id WHERE kill_point>0 ORDER BY (kill_point-die_point) DESC LIMIT 10", .@name$, .@kill, .@die, .@total;

[Ind]

start storing the char name in the same table, much more efficient.

[XenaNyx]

query_sql "SELECT `char`.`name`, `kill_point`, `die_point`, `kill_point`-`die_point` FROM `pvp_ranking` LEFT JOIN `char` ON pvp_ranking.char_id=`char`.char_id WHERE kill_point>0 ORDER BY (kill_point-die_point) DESC LIMIT 10", .@name$, .@kill, .@die, .@total;

same error

SQL: DB error - Column 'die_point' in field list is ambiguous

start storing the char name in the same table, much more efficient.

i think too but i want to know how to write this script

[Brian]

I tested it using the CREATE TABLE and INSERT statements in your 1st post and I didn't get any warnings.

Did you add a `die_point` column to your `char` table?

If you're not using it, you should drop that column.

Or you can fully qualify the table.column names like this:

SELECT `char`.`name`, pvp_ranking.kill_point, pvp_ranking.die_point, pvp_ranking.kill_point-pvp_ranking.die_point 
FROM `pvp_ranking` LEFT JOIN `char` ON pvp_ranking.char_id=`char`.char_id 
WHERE pvp_ranking.kill_point>0 ORDER BY (pvp_ranking.kill_point - pvp_ranking.die_point) DESC LIMIT 10;

[XenaNyx]

I tested it using the CREATE TABLE and INSERT statements in your 1st post and I didn't get any warnings.

Did you add a `die_point` column to your `char` table?

If you're not using it, you should drop that column.

Or you can fully qualify the table.column names like this:

SELECT `char`.`name`, pvp_ranking.kill_point, pvp_ranking.die_point, pvp_ranking.kill_point-pvp_ranking.die_point
FROM `pvp_ranking` LEFT JOIN `char` ON pvp_ranking.char_id=`char`.char_id
WHERE pvp_ranking.kill_point>0 ORDER BY (pvp_ranking.kill_point - pvp_ranking.die_point) DESC LIMIT 10;

i add die_point already but i don't know why i have error

you code it's work thanks a lot

[Brian]

Ah, that's why then.

SQL: DB error - Column 'die_point' in field list is ambiguous

You have the same column name (die_point) in 2 tables.

When you have a query that uses both of those tables and `die_point`, it doesn't know if you want to use char.die_point or pvp_ranking.die_point

That's why you have to use the full name: table.column