Matching two rows in a single table if two specific keys have different values

Question

I have a table called settings in that there are two columns and data like following.

settings           value
-------            ------
depots             12
projectid          156
client             icici bank
clientvisits       2
DBname             icicibank
progress           true
displayvisits      2
visitsskipped      0

Here , the values of rows clientvisits and displayvisits should be same always. I want a query which display the only these two fields along with values if their values are different. That means if clientvisits=2 and displayvisits=4 then it should throw an error (I mean it should display the output along with values).

I tried by using self join like this but it is not working.

select * from settings TS,settings TS1
where TS.settings=TS1.settings and CONVERT(integer,ts.value)=1

select * from settings TS,settings TS1
where TS.settings=TS1.settings and ts.setting like 'visitstodisplay'=1 

Also, I tried to do some thing else but I didn't get. Please let me know how to do it.

Answer 1

You can do a join on Value <> Value for the settings you are looking for.

select S1.Settings as Settings1,
       S1.Value as Value1,
       S2.Settings as Settings2,
       S2.Value as Value2
from Settings as S1
  inner join Settings as S2
    on S1.Value <> S2.Value and
       S1.Settings = 'clientvisits' and
       S2.Settings = 'displayvisits' 

Result:

Settings1            Value1               Settings2            Value2
-------------------- -------------------- -------------------- --------------------
clientvisits         2                    displayvisits        4         

Answer 2

SELECT * FROM (SELECT * FROM Settings WHERE Settings = 'displayvisits') d
INNER JOIN (SELECT * FROM Settings WHERE Settings = 'clientvisits') c ON d.Value <> c.Value

Will return you both settings in a single row.

Answer 3

select
    TS.clientvisits,
    TS.displayvisits 
from settings TS,settings TS1
where 
    TS.settings=TS1.settings 
    and TS.clientvisits!=TS.displayvisits