Mysql – Adding a column to aggregated data where new value only applies to a subset of the aggregation

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.

Like Mike says, this is not something that you should be doing with the database. This is more of an algorithm question.

Your problem looks very similar to the knapsack problem, which is NP-Complete. Fortunately, you are not looking for the set that adds up to exactly some amount of calories, but a range, so it's not NP-Complete. Luckily for you, a few hundred recipes will easily fit in memory, so you can easily write an algorithm for it.

Here's a simple algorithm. I'm sure there's a better way to do it:

orderedRecipes = recipes sorted by calorie asc

def combinations(recipes, lowerBound, upperBound, recipeCount):
    first = recipes[0]
    if first.calorie < upperBound:
        for c in combinations(recipes[1:], lowerBound - first.calorie, upperBound - first.calorie, recipeCount - 1):
            if sum(r.calorie for r in [first] + c) > lowerBound:
                yield [first] + c
        for c in combinations(recipes[1:], lowerBound, upperBound, recipeCount):
            if sum(r.calorie for r in c) > lowerBound:
                yield c

combinationsOf3Between440and460 = list(combinations(orderedRecipes, 440, 460, 3))

The first loop takes the first item in the rest of the list, and the second loop does not. You don't want it to always take the first item, you want both take and no-take.