Avoiding using the same subquery multiple times in a query

Question

On the MMORPG server I am refactoring, I have two tables. One for items and one for spells. Each item has up to 5 spells, so I went with a sparse matrix format, having 5 columns for spell identifiers.

The original developers of this structure preferred to use MyISAM, which does not support links, as a result of which the table of elements contains elements with non-existent spell identifiers. I want to find out which elements have the wrong spell identifiers in order to fix them and possibly eventually convert to InnoDB.

So far, I could only come up with the following:

SELECT COUNT(*)
  FROM items
 WHERE spellid_1 NOT IN (SELECT entry FROM research.spell)
    OR spellid_2 NOT IN (SELECT entry FROM research.spell)
    OR spellid_3 NOT IN (SELECT entry FROM research.spell)
    OR spellid_4 NOT IN (SELECT entry FROM research.spell)
    OR spellid_5 NOT IN (SELECT entry FROM research.spell);

Is there a more elegant way to do this?

EDIT: NULL spellid_n is considered valid, as it simply means that the item does not have a spell in this slot.

Answer 1

It would be more elegant to design tables so that you do not have 5 bell columns in one table - for example, having an item_spell table that would allow you to use any number of spells for each element. Besides the fact that you hope more for the future (if you need 6 spells now), your request will be as follows:

SELECT COUNT(DISTINCT item_id)
  FROM item_spells
 WHERE spell_id NOT IN (SELECT entry FROM research.spell);

Be that as it may, you are forced to perform a check 5 times.

Answer 2

( -). , , , , , .

sql 5 , . SQL '99 'with':

WITH   spellids(entry) AS SELECT entry FROM research.spell
SELECT COUNT(*)  
FROM   items
WHERE     spellid_1 NOT IN spellids OR spellid_2 NOT IN spellids
       OR spellid_3 NOT IN spellids OR spellid_4 NOT IN spellids
       OR spellid_5 NOT IN spellids ;

, , , MySQL 'with' (. ).

Answer 3

, . :

SELECT  COUNT(*)
FROM items i
LEFT JOIN research.spell spell1 ON i.spellid_1 = spell1.entry
LEFT JOIN research.spell spell2 ON i.spellid_2 = spell2.entry
LEFT JOIN research.spell spell3 ON i.spellid_3 = spell3.entry
LEFT JOIN research.spell spell4 ON i.spellid_4 = spell4.entry
LEFT JOIN research.spell spell5 ON i.spellid_5 = spell5.entry
WHERE spell1.entry IS NULL 
OR spell2.entry IS NULL 
OR spell3.entry IS NULL
OR spell4.entry IS NULL
OR spell5.entry IS NULL

, research.spell, , JOIN. , IS NULL. () (research.spell).

EDIT:

, SELECT COUNT (*) . 1 . SELECT i.id - , , .

Answer 4

"reverse not in", :

SELECT COUNT(*)  
FROM items 
WHERE (SELECT entry FROM research.spell) NOT IN (spellid_1, spellid_2, 
                                                 spellid_3, spellid_4, 
                                                 spellid_5)

a >

EDIT: ah thought only 1 value. then you can do this in an inner join:

SELECT  COUNT(*)  
FROM    items i
    join (SELECT entry FROM research.spell) t 
          on t.entry NOT IN (spellid_1, spellid_2, spellid_3, spellid_4, spellid_5)