Incorrect results while using multiple conditions in where clause in SQL
<p>I have the below query where I need to filter data based on multiple where conditions, but in the result set I get incorrect results not satisfying the conditions. What am I doing wrong?</p>

<pre><code>SELECT TOP 1 a.ID FROM Counselor a
            inner join levelMapping b on  a.UserLevel=b.LevelMapped
            inner join mUser c on a.ID=c.ID
            WHERE               

            a.HomeLocation=@RES_location  and a.PrimarySkill=@RES_PrimarySkill and
            b.LEVELINDEX&lt;= 
            --8
            (case when @RES_levelIndex in (8,9) then @RES_levelIndex-1 else @RES_levelIndex-2 end)     
            and MappedCC_Count&lt;=10 and a.CUrrLoc not like '%Out Of India%' and a.CUrrLoc not like '%Client%'
            and c.AG='XYZ'

            order by b.levelindex desc,
            --case when @RES_levelIndex=9 then b.levelindex end asc,
            --case when @RES_levelIndex &gt; 9 then b.levelindex end desc,
                  a.MappedCC_Count asc  
</code></pre>

<p>Example:
The result below shows Conselor(CC) with different location and skill has been mapped to user although our logic does not allow this:</p>

<p><a href="https://i.stack.imgur.com/DP68D.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DP68D.png" alt="enter image description here"></a></p>