Listing Missing Table Item

The following example is based on the post:

microsoft.public.sqlserver.programming
Friday, November 21, 2008 11:56 AM
"T-SQL Comparing a String of Items to a Table of Items; Listing Missing Table Items"
http://tinyurl.com/5dvc6o

Here's what the future of application development will hopefully be like
using a relational system. To program relationally you not only have to
think in "terms of sets" (the declarative thing:) but you have to think
"in terms of type" (the relation thing). Sql essentially doesn't concern
itself with variables and types. They are essentially viewed as foreign
thingies found in net. But in a relational system they are every bit as 
significant as understanding how to write an outer join. The following
example illustrates the relational approach using the Dataphor system
(which can play nicely with Sql Server ☺)

create session table Products {
  product_key:Integer,
  part_no: String,
  options: list(String), //options is defined as a list type, not a string.
  price: Money,
  key{product_key}
  };

All tables, like numbers, are variables with an explicit 'table' type, the
column names and their datatypes. And like integers they can be 'assigned'
values. Tables are composed of 'rows'. For each row options is input as a
'list' of type string ({'<string>','<string>','<string>'..}).

Products:=
table {
      row{11 product_key, 'ASP-20J' part_no, {'Wiz Bang', 'Hopla Enhancer'} options, $10.00 price},
      row{12, 'ASP-20R', {'Widget Holder','Wiz Bang',  'Hopla Enhancer'}, $12.00}
      }; 

create session table Options {
  option_key: Integer,
  product_key: Integer, 
  option: String,
  key{option_key},
  reference OptiontoProduct {product_key} references Products {product_key}
  };

Options:=
table {
       row{5 option_key, 11 product_key, 'Wiz Bang' option},
       row{6, 11, 'Hopla Enhancer'},
       row{7, 12, 'Wiz Bang'},
       row{8, 12, 'Hopla Enhancer'}
      };
       
Here are easy ways to find the missing product from the Options table.

Using row in table. First a table of numbers (Numbers) with a single
column N (from 0-10K) is used to create a row for each element in
the options list bringing along the other Products columns. The row
is constructed from this table to see if it's not in options. 

select 
   (
    Products 
       times  //Like an sql cross join. 
         Numbers
           where N<options.Count()
              //create a row for each element in the list.
             {product_key,options[N] option} 
                                           
    )     
       where not (
                  row{product_key product_key,option option}
                  in
                 (Options {product_key,option})
                 ) ;
                
Using relational division. Because tables (table expressions) are
variables one table can be tested to see if it's contained in another.

select 
   (
    Products times Numbers
         where N<options.Count()
          {product_key,options[N] option}
    )     
       where not (  
               table{row{product_key product_key,option option}}
                <=
               (Options {product_key,option})
               ) ;                 
               
Using a left join. Test whether the table on the right has a matching row.

select 
   (
    Products times Numbers
         where N<options.Count()
          {product_key,options[N] option}
    )     
      left join Options
                include rowexists  //A special Boolean column for whether there is a match.
                  where not rowexists 
                    {product_key,option} ;
      
Instead of inputting a list directly, a delimited string can be converted to
a list when input.

Products:=
table{
      row{11 product_key, 'ASP-20J' part_no, ('Wiz Bang, Hopla Enhancer '.Split()) options, $10.00 price},
      row{12, 'ASP-20R', (' Widget Holder ,Wiz Bang,  Hopla Enhancer'.Split()), $12.00}
      }; 
Options:=
table{
      row{5 option_key, 11 product_key, 'Wiz Bang' option},
      row{6, 11, 'Hopla Enhancer'},
      row{7, 12, 'Wiz Bang'},
      row{8, 12, 'Hopla Enhancer'}
     };

The queries are the same as above except for trimming each element of the list.

select 
   (
    Products times Numbers
         where N<options.Count()
          {product_key,options[N].Trim() option}
    )     
     where not (
                row{product_key product_key,option option}
                in
               (Options {product_key,option})
                ) ;
                
select 
   (
    Products times Numbers
         where N<options.Count()
          {product_key,options[N].Trim() option}
    )     
     where not (  
               table{row{product_key product_key,option option}}
                <=
               (Options {product_key,option})
               ) ;                 
               
select 
   (
    Products times Numbers
         where N<options.Count()
          {product_key,options[N].Trim() option}
    )     
      left join Options
                include rowexists 
                  where not rowexists 
                    {product_key,option};
                    
All queries produce a result of:

product_key option        
----------- ------------- 
12          Widget Holder 

Types eliminate violations of normal forms much like education eliminates ignorance ☺

Dataphor is a RAD tool, R(elational) A(ccelerator) D(evelopment).
Visit dataphor at:
www.dataphor.org