Q:

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value'
I have 2 tables...
Table 1 is called accounts
Table 2 is called inventoryitems
The column in question for accounts is called banned
The column in question for inventoryitems is called itemid
I would like to DELETE FROM inventoryitems WHERE itemid = 2340000
IF...
the column banned in accounts has a value of 1
You can join the table accounts to inventoryitems by a 3rd table called characters.
Table accounts has columns: id (primary key) and banned.
Table characters has columns: characterid and accountid (accountid links to id in table accounts).
Table inventoryitems has columns itemid and characterid (characterid links to characterid in table characters)
Hope I have provided enough information...
I have tried:
DELETE FROM inventoryitems 
WHERE characterid IN 
    (SELECT characterid 
     from characters 
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000'; 

However this deletes all rows in the inventoryitems table where itemid = '234000'. Seems to still ignore the check for '1' in banned column of accounts table.... I think this is close though...
Here are the SHOW CREATE TABLE queries for the 3 tables:
'accounts', 'CREATE TABLE `accounts` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(13) NOT NULL default '',
  `password` varchar(128) NOT NULL default '',
  `salt` varchar(32) default NULL,
  `loggedin` tinyint(4) NOT NULL default '0',
  `lastlogin` timestamp NULL default NULL,
  `createdat` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `birthday` date NOT NULL default '0000-00-00',
  `banned` tinyint(1) NOT NULL default '0',
  `banreason` text,
  `gm` tinyint(1) NOT NULL default '0',
  `email` tinytext,
  `emailcode` varchar(40) default NULL,
  `forumaccid` int(11) NOT NULL default '0',
  `macs` tinytext,
  `lastknownip` varchar(30) NOT NULL default '',
  `lastpwemail` timestamp NOT NULL default '2002-12-31 17:00:00',
  `tempban` timestamp NOT NULL default '0000-00-00 00:00:00',
  `greason` tinyint(4) default NULL,
  `paypalNX` int(11) default NULL,
  `mPoints` int(11) default NULL,
  `cardNX` int(11) default NULL,
  `webadmin` int(1) default '0',
  `lastlogininmilliseconds` bigint(20) unsigned NOT NULL default '0',
  `guest` tinyint(1) NOT NULL default '0',
  `donorpoints` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `forumaccid` (`forumaccid`),
  KEY `ranking1` (`id`,`banned`,`gm`)
) ENGINE=InnoDB AUTO_INCREMENT=753 DEFAULT CHARSET=latin1'

'characters', 'CREATE TABLE `characters` (
  `id` int(11) NOT NULL auto_increment,
  `accountid` int(11) NOT NULL default '0',
  `world` int(11) NOT NULL default '0',
  `name` varchar(13) NOT NULL default '',
  `level` int(11) NOT NULL default '0',
  `exp` int(11) NOT NULL default '0',
  `str` int(11) NOT NULL default '0',
  `dex` int(11) NOT NULL default '0',
  `luk` int(11) NOT NULL default '0',
  `int` int(11) NOT NULL default '0',
  `hp` int(11) NOT NULL default '0',
  `mp` int(11) NOT NULL default '0',
  `maxhp` int(11) NOT NULL default '0',
  `maxmp` int(11) NOT NULL default '0',
  `meso` int(11) NOT NULL default '0',
  `hpApUsed` int(11) NOT NULL default '0',
  `mpApUsed` int(11) NOT NULL default '0',
  `job` int(11) NOT NULL default '0',
  `skincolor` int(11) NOT NULL default '0',
  `gender` int(11) NOT NULL default '0',
  `fame` int(11) NOT NULL default '0',
  `hair` int(11) NOT NULL default '0',
  `face` int(11) NOT NULL default '0',
  `ap` int(11) NOT NULL default '0',
  `sp` int(11) NOT NULL default '0',
  `map` int(11) NOT NULL default '0',
  `spawnpoint` int(11) NOT NULL default '0',
  `gm` tinyint(1) NOT NULL default '0',
  `party` int(11) NOT NULL default '0',
  `buddyCapacity` int(11) NOT NULL default '25',
  `createdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `rank` int(10) unsigned NOT NULL default '1',
  `rankMove` int(11) NOT NULL default '0',
  `jobRank` int(10) unsigned NOT NULL default '1',
  `jobRankMove` int(11) NOT NULL default '0',
  `guildid` int(10) unsigned NOT NULL default '0',
  `guildrank` int(10) unsigned NOT NULL default '5',
  `messengerid` int(10) unsigned NOT NULL default '0',
  `messengerposition` int(10) unsigned NOT NULL default '4',
  `reborns` int(11) NOT NULL default '0',
  `mountlevel` int(9) NOT NULL default '1',
  `mountexp` int(9) NOT NULL default '0',
  `mounttiredness` int(9) NOT NULL default '0',
  `petid` int(10) default '0',
  `married` int(10) unsigned NOT NULL default '0',
  `partnerid` int(10) unsigned NOT NULL default '0',
  `cantalk` int(10) unsigned NOT NULL default '1',
  `marriagequest` int(10) unsigned NOT NULL default '0',
  `omokwins` int(11) NOT NULL default '0',
  `omoklosses` int(11) NOT NULL default '0',
  `omokties` int(11) NOT NULL default '0',
  `matchcardwins` int(11) NOT NULL default '0',
  `matchcardlosses` int(11) NOT NULL default '0',
  `matchcardties` int(11) NOT NULL default '0',
  `MerchantMesos` int(11) default '0',
  `HasMerchant` tinyint(1) default '0',
  `equipslots` int(11) NOT NULL default '48',
  `useslots` int(11) NOT NULL default '48',
  `setupslots` int(11) NOT NULL default '48',
  `etcslots` int(11) NOT NULL default '48',
  `allianceRank` int(10) unsigned NOT NULL default '5',
  `clan` tinyint(1) NOT NULL default '-1',
  `pvpkills` int(1) NOT NULL default '0',
  `pvpdeaths` int(1) NOT NULL default '0',
  `omok` int(4) default NULL,
  `matchcard` int(4) default NULL,
  `zakumlvl` int(10) unsigned NOT NULL default '0',
  `gmtext` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `accountid` (`accountid`),
  KEY `party` (`party`),
  KEY `ranking1` (`level`,`exp`),
  KEY `ranking2` (`gm`,`job`)
) ENGINE=InnoDB AUTO_INCREMENT=30792 DEFAULT CHARSET=latin1'

'inventoryitems', 'CREATE TABLE `inventoryitems` (
  `inventoryitemid` int(10) unsigned NOT NULL auto_increment,
  `characterid` int(11) default NULL,
  `storageid` int(10) unsigned default NULL,
  `itemid` int(11) NOT NULL default '0',
  `inventorytype` int(11) NOT NULL default '0',
  `position` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  `owner` tinytext NOT NULL,
  `petid` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`inventoryitemid`),
  KEY `inventoryitems_ibfk_1` (`characterid`),
  KEY `characterid` (`characterid`),
  KEY `inventorytype` (`inventorytype`),
  KEY `storageid` (`storageid`),
  KEY `characterid_2` (`characterid`,`inventorytype`),
  CONSTRAINT `inventoryitems_ibfk_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25799280 DEFAULT CHARSET=latin1'

A:

Your statement seems to be correct. It could also be written like this:
DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
    ) ; 

One thing that may be causing this is if you have a character related to many accounts and one of them has banned = 1 while the other have banned = 0. I assume you want the deletion to happen (not with just one but) only if all the related accounts have banned = 1. We can modify the above code to:
DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
        AND NOT EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 0
            )
    ) ; 

or simpler to:
DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
        JOIN accounts AS a
          ON a.id = c.accountid
      WHERE c.characterid = ii.characterid 
      HAVING MIN(a.banned) = 1
    ) ; 

After the clarifications, all the above are void. The problem was that characters table does not have characterid column but only id. So the statement used by the OP was translated/parsed as:
DELETE FROM inventoryitems
WHERE characterid IN 
    (SELECT inventoryitems.characterid         -- notice this
     from characters
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000'; 

which makes the subquery uncorrelated and means "delete all rows with itemid = 2340000" if there exists at least one row (any row, not necessarily related) in the accounts with banned=1"
That's one reason why it's good to always (*) write columns with their full name as tablename.columnname or tablealias.columnname (an error would have been thrown if you had done this and problem would have been solved faster.) 
(*) Unless one wants this behaviour to occur, which is a rather extreme case.

