how to use condition to check 2 things and when the 2 are true, it give an information

Question

I made a code to tell me for a rpg game, how many player are using a specific class (so i check first hand, sword, and second hand, sword too, and it should tell me the name and level of any player above level 100 with a sword in both hands, here is the code :

declare @level varchar(100)
set @level=80

select level,name from characters c
inner join items i on i.characterId=c.characterId
where
( level>@level AND equipSlot=5 and ( 
itemId=2002 OR --short sword
itemId=2005 OR --steel rapier
itemId=2010 OR --volt sword
itemid=2012 OR --goblin knife
itemId=2017 OR --crimson sword
itemId=2026 OR --tree splitter sword
itemId=2031 OR --sea king sword
itemId=2034 OR --acrodont blade
itemid=2054 OR --halloween sword
itemId=2059 --stinger
))
and
(level>@level and equipSlot=6 and ( 
itemId=2002 OR --short sword
itemId=2005 OR --steel rapier
itemId=2010 OR --volt sword
itemid=2012 OR --goblin knife
itemId=2017 OR --crimson sword
itemId=2026 OR --tree splitter sword
itemId=2031 OR --sea king sword
itemId=2034 OR --acrodont blade
itemid=2054 OR --halloween sword
itemId=2059 --stinger
))
ORDER by level desc

So i check first hand (equipslot=6), check if one of the available sword is equiped, then if the second hand (equipslot=5) has a sword too, it means the guy is using 2 sword class.

But it doesn't do anything when I put an and between the 2 parenthesis. And it shows all peoples using a sword in first hand when i use or, so peoples who aren't using 2 sword appear too.

I can't figure out how I can select the player name and level once (because when they use 2 swords their name appear twice and not once), so checking both their hand, and if they have sword in both hand, show their name and level.

Answer 1

You want to check if groups of items that belong to a given character satisfy the conditions, which suggests aggregation:

select c.name 
from characters c
inner join items i on i.characterId=c.characterId
where 
    i.level > @level
    and i.equipslot in (5, 6)
    and i.itemid in (2002, 2005, 2010, 2012, 2017, 2026, 2031, 2034, 2054, 2059)
group by c.name
having count(distinct equipslot) = 2

If you want to display the level of each hand, you can do:

select c.name, 
    max(case when i.equipslot = 5 then level end) level_hand_5,
    max(case when i.equipslot = 6 then level end) level_hand_6
from characters c
inner join items i on i.characterId=c.characterId
where 
    i.level > @level
    and i.equipslot in (5, 6)
    and i.itemid in (2002, 2005, 2010, 2012, 2017, 2026, 2031, 2034, 2054, 2059)
group by c.name
having count(distinct equipslot) = 2

Notes:

• it is a good practice in a multi-table query to prefix each column with the table it belongs to; I made a few assumptions that you might need to review

• in comes handy to shorten the multiple or conditions