How do I select multiple rows using multiple columns without using multiple subqueries

Question

I have the following query:

SELECT name, modelName, color, rarity, slot, power, chapter
FROM items
WHERE items.id = (SELECT head FROM characterdata WHERE steamid = ?)
OR items.id = (SELECT leftHand FROM characterdata WHERE steamid = ?)
OR items.id = (SELECT rightHand FROM characterdata WHERE steamid = ?)
OR items.id = (SELECT feet FROM characterdata WHERE steamid = ?);

It has to perform the exact same query four times which seems bad practice. How could I rewrite it so it only uses two queries, aka so it only has to get the characterdata once?

Answer 1

You can use exists:

SELECT name, modelName, color, rarity, slot, power, chapter
FROM items i
WHERE EXISTS (SELECT 1
              FROM characterdata cd
              WHERE cd.steamid = ? AND
                    i.id IN (cd.head, cd.leftHand, cd.rightHand, cd.feet)
             );

This assumes that ? always has the same value.

If not, you could express this as:

SELECT name, modelName, color, rarity, slot, power, chapter
FROM items i
WHERE EXISTS (SELECT 1
              FROM characterdata cd
              WHERE (cd.steamid = ? AND i.id = cd.head) OR
                    (cd.steamid = ? AND i.id = cd.leftHand) OR
                    (cd.steamid = ? AND i.id = cd.rightHand) OR
                    (cd.steamid = ? AND i.id = cd.feet)
             );