Query_sql error

[nitrous]

Hey guys, looks like I need some help.

I have 3 tables, mob_db_re, mob_drop_re, and item_db_re.
mob_drop_re is created with this ddl statement:

CREATE TABLE `mob_drop_re` (
    `mobid` mediumint(9) unsigned NOT NULL default '0',
    `dtype` smallint(5) unsigned NOT NULL default '0',
    `index` smallint(5) unsigned NOT NULL,
    `nameid` smallint(5) unsigned NOT NULL default '0',
    `rate` smallint(9) unsigned NOT NULL default '0',
    `stealProtected` bool default false,
    `randomOptionGroup` text,

    PRIMARY KEY (`mobId`, `dtype`, `index`)

) ENGINE=MyISAM;

 

I'm working on the card_seller in npc/custom/card_seller.txt, and I get an error at the first query_sql call. Here's the code:

OnInit:
    if (checkre(0)) {
        .@mob_db$  = "mob_db_re";
        .@mob_drop$ = "mob_drop_re";
        .@item_db$ = "item_db_re";
    } else {
        .@mob_db$  = "mob_db";
        .@mob_drop$ = "mob_drop";
        .@item_db$ = "item_db";
    }
    freeloop 1;
    .@total = query_sql("SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 ORDER BY alphabets;", .alphabet$);
    for ( .@i = 0; .@i < .@total; .@i++ ) {
        .alphabet_menu$ = .alphabet_menu$ + .alphabet$[.@i] +" Cards:";
        .@nb = query_sql("SELECT `"+.@item_db$ +"`.`id` FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 AND LEFT(`name_japanese`, 1) = '"+ .alphabet$[.@i]+"' GROUP BY `name_japanese` ORDER BY `name_japanese` LIMIT 128;", .@id);
        npcshopdelitem "card_mob#"+ .alphabet$[.@i], 501;
        for ( .@j = 0; .@j < .@nb; .@j++ )
            npcshopadditem "card_mob#"+ .alphabet$[.@i], .@id[.@j], 1000000;
    }
    freeloop 0;
    end;

 

The script creates a sql statement as so:

 

SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `item_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid` WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &0x4000000 ORDER BY alphabets;

 

It works fine on MySQL Workbench, but produces an error when using it in the script:

[SQL]: DB error - Unknown column 'mob_drop_re.nameid' in 'on clause'
[Debug]: at script.cpp:16666 - SELECT DISTINCT LEFT(`name_japanese`, 1) AS alpha
bets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `ite
m_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid`
 WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &
0x4000000 ORDER BY alphabets;
[Debug]: Source (NPC): Card Seller at prontera (155,177)

 

Can anyone help?

[nitrous]

Well I'm dumb, I'm connecting to the wrong database. Please ignore this post.

[GodKnows Jhomz]

6 hours ago, Nitrous said:

Hey guys, looks like I need some help.

I have 3 tables, mob_db_re, mob_drop_re, and item_db_re.
mob_drop_re is created with this ddl statement:

CREATE TABLE `mob_drop_re` (
    `mobid` mediumint(9) unsigned NOT NULL default '0',
    `dtype` smallint(5) unsigned NOT NULL default '0',
    `index` smallint(5) unsigned NOT NULL,
    `nameid` smallint(5) unsigned NOT NULL default '0',
    `rate` smallint(9) unsigned NOT NULL default '0',
    `stealProtected` bool default false,
    `randomOptionGroup` text,

    PRIMARY KEY (`mobId`, `dtype`, `index`)

) ENGINE=MyISAM;

 

I'm working on the card_seller in npc/custom/card_seller.txt, and I get an error at the first query_sql call. Here's the code:

OnInit:
    if (checkre(0)) {
        .@mob_db$  = "mob_db_re";
        .@mob_drop$ = "mob_drop_re";
        .@item_db$ = "item_db_re";
    } else {
        .@mob_db$  = "mob_db";
        .@mob_drop$ = "mob_drop";
        .@item_db$ = "item_db";
    }
    freeloop 1;
    .@total = query_sql("SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 ORDER BY alphabets;", .alphabet$);
    for ( .@i = 0; .@i < .@total; .@i++ ) {
        .alphabet_menu$ = .alphabet_menu$ + .alphabet$[.@i] +" Cards:";
        .@nb = query_sql("SELECT `"+.@item_db$ +"`.`id` FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 AND LEFT(`name_japanese`, 1) = '"+ .alphabet$[.@i]+"' GROUP BY `name_japanese` ORDER BY `name_japanese` LIMIT 128;", .@id);
        npcshopdelitem "card_mob#"+ .alphabet$[.@i], 501;
        for ( .@j = 0; .@j < .@nb; .@j++ )
            npcshopadditem "card_mob#"+ .alphabet$[.@i], .@id[.@j], 1000000;
    }
    freeloop 0;
    end;

 

The script creates a sql statement as so:

 

SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `item_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid` WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &0x4000000 ORDER BY alphabets;

 

It works fine on MySQL Workbench, but produces an error when using it in the script:

[SQL]: DB error - Unknown column 'mob_drop_re.nameid' in 'on clause'
[Debug]: at script.cpp:16666 - SELECT DISTINCT LEFT(`name_japanese`, 1) AS alpha
bets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `ite
m_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid`
 WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &
0x4000000 ORDER BY alphabets;
[Debug]: Source (NPC): Card Seller at prontera (155,177)

 

Can anyone help?

What is the expected result in modifying this card seller?

Edited January 28, 2018 by GodKnows Jhomz

[nitrous]

To make the automated build work.

I split the mobdb and mobdrop tables in the database, which requires me to fix this script.

The first SQL statement should return a list of letters in alphabetical order so that there exists at least one normal monster that drops a card that starts with that letter.