How to compare values within the same table in TSQL elegantly

Question

I've got a settings table constructed with 'category' determining different products, 'varname' being the variable name, and 'info' being the value of the setting.

so, for instance,

select top 6 category, varname, info 
from settings_table 
where NODE_NAME='HTT-COMM-A' 
  and section='Module Settings' 
  and category in  ('ProductA', 'ProductB') 
order by varname

results in :

 category   varname             info  
 ProductB   WEB_ACCESS_ALLOW    NO  
 ProductA   WEB_ACCESS_ALLOW    NO  
 ProductB   WEB_ACCESS_BLOCK    YES  
 ProductA   WEB_ACCESS_BLOCK    YES  
 ProductB   WEB_ACCOUNT_DETAIL  NO  
 ProductA   WEB_ACCOUNT_DETAIL  YES  

I'd like to generate a simple list of differences between the values when category='ProductA' and 'ProductB'. I can think of a number of ways to do this with a temporary table, or by a number of subselects (for instance, this painful one) :

select a.category, a.varname, a.info , b.category, b.info 
from (select category, varname, info, description
      from settings_table 
      where category = 'ProductA') as a,
     (select category, varname,info, description 
      from settings_table 
      where category = 'ProductB') as b 
where a.varname=b.varname and a.info != b.info

but the above method (at least) fails when there's a varname in b that isn't in a. (Any solutions should fix this problem, any differences in varnames between a and b should be represented as well.)

This isn't a hard problem to solve in a kludgy way, but I wonder if there's a 'right way' to do this elegantly, without the horrid sub-selects or without the caveats above.

This is more SQL agnostic, but this particular table is in a MSSQL server.

Thanks, Rk

Answer 1

You can use a self-join to accomplish this:

select a.varname as varname,
a.info as 'ProductA_Setting',
b.info as 'ProductB_Setting'
from @t a
inner join @t b
on a.varname = b.varname
where a.category = 'ProductA'
and b.category = 'ProductB'
and a.info <> b.info

Here's the script I used to test:

declare @t table (category varchar(32), varname varchar(32), info varchar(32))

insert into @t select 'ProductB', 'WEB_ACCESS_ALLOW', 'NO'
insert into @t select 'ProductA', 'WEB_ACCESS_ALLOW', 'NO'
insert into @t select 'ProductB', 'WEB_ACCESS_BLOCK', 'YES'
insert into @t select 'ProductA', 'WEB_ACCESS_BLOCK', 'YES'
insert into @t select 'ProductB', 'WEB_ACCOUNT_DETAIL', 'NO'
insert into @t select 'ProductA', 'WEB_ACCOUNT_DETAIL', 'YES'

select * from @t

select a.varname as varname,
a.info as 'ProductA_Setting',
b.info as 'ProductB_Setting'
from @t a
inner join @t b
on a.varname = b.varname
where a.category = 'ProductA'
and b.category = 'ProductB'
and a.info <> b.info

Answer 2

You'll want to use a CTE and a full outer join for this, I think:

WITH SETTINGS (category, varname, info)
AS
(
    SELECT category, varname, info
    FROM settings_table
    WHERE NODE_NAME = 'HTT-COMM-A'
        AND [section] = 'Module Settings'
        AND category IN ('ProductA', 'ProductB')
)
SELECT
    COALESCE(A.varname, B.varname) AS varname,
    A.info AS info_a,
    B.info AS info_b
FROM
    SETTINGS A
    FULL OUTER JOIN SETTINGS B
        ON A.category = 'ProductA'
            AND B.category = 'ProductB'
            AND A.varname = B.varname
WHERE
    A.varname IS NULL
    OR B.varname IS NULL    
    OR A.info!= B.info
ORDER BY
    COALESCE(A.varname, B.varname)

Answer 3

If you only cared about the varname and info values, you could do something like:

Select varname, info
From @Data As T
Except  (
        Select varname, info
        From @Data As T1
        Where category = 'ProductA'
        Intersect
        Select varname, info
        From @Data As T2
        Where category = 'ProductB'
        )

If you wanted other columns from the source table, then you can do something like:

Select T.*
From settings_table As T
    Left Join   (
                Select T1.varname, T1.info
                From settings_table As T1
                Where T1.category = 'ProductA'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                Intersect
                Select T2.varname, T2.info
                From settings_table As T2
                Where T1.category = 'ProductB'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                ) As Z
        On Z.varname = T.varname
            And Z.info = T.info
Where Z.varname Is Null
    And T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'

Yet a third way would be to simply use an EXISTS predicate:

Select T.*
From settings_table As T
Where T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'
    And Not Exists  (
                    Select 1
                    From settings_table As T2
                    Where T2.category In('ProductA','ProductB')
                        And T2.varname = T.varname
                        And T2.info = T.info
                    Group By T2.varname, T2.info
                    Having Count(*) = 2
                    )

Answer 4

SELECT... EXCEPT and SELECT...INTERSECT always qualify as elegant in my book, but that doesn't necessarily make code neater or easier to read, and the version I came up with still contains subqueries.

Based on the temp table from Paul Kearney - pk, I came up with:

DECLARE
  @Category1 varchar(32)
 ,@Category2 varchar(32)

SET @Category1 = 'ProductA'
SET @Category2 = 'ProductB'

SELECT isnull(set1.varname, set2.varname) varname, set1.Category, set1.Info, set2.Category, set2.Info
 from (--  Exists for "1" but not for "2"
       select @Category1 Category, varname, info
        from @t
        where category = @Category1
       except select @Category1, varname, info
        from @t
        where category = @Category2) set1
  full outer join (--  Exists for "2" but not for "1"
                   select @Category2 Category, varname, info
                     from @t
                     where category = @Category2
                    except select @Category2, varname, info
                     from @t
                     where category = @Category1) set2
   on set2.varname = set1.varname

The full outer join catches missing rows, and you end up with some NULLs in the Category and Info columns.

Answer 5

You've discovered just one of the many problems with an Entity-Attribute-Value Data Model. To programmers this model is very enticing... it seduces you with promises of ease and simplicity. "Look, I can add a new settings with no DDL!" Whoa, too cool. But a record in that table doesn't DO anything, you're still adding code to look for that setting, and then use that setting. With all that work, is adding a new column really a huge pain?

A settings table is about the only thing you MIGHT be able to excuse for an EAV, but why?

To answer your question by teaching you to fish, instead of handing you a Fil'a'o'fish...

Google => SQL JOIN TYPES

Read them all but focus on Outer and Full Outer joins