Sql - Really simple division

This article is based on the thread:

microsoft.public.sqlserver.programming
Monday, October 22, 2007
"select rows that contain both arg1 = 'x' and arg1 = 'y'"
http://tinyurl.com/386lbx

>I need to select only rows where the name contains x and y.  I do not want
>to get rows that only contain x or contain x, z, or contain y, z.  I only
>want rows where the name has an x an a y (they can have a z but they must
>have an x and a y).

Forget QA or SSMS. Just draw a picture.
You use the term row. So draw a row.

row{aName as sName, acode as code}

Now you want people who have code x and y. These are two
rows. So draw them. Use 'burns' for the person.

row{'burns' as sName, 'x' as code}
row{'burns' as sName, 'y' as code}

Now what do you call two rows together? How about calling it
a table ☺ .
Draw it.

table
   {
    row{'burns' as sName, 'x' as code}
    row{'burns' as sName, 'y' as code}
   }
 
Now if this table, made just for 'burns', is in your
#tmp1 table then 'burns' is a guy you want. In other
words, if both rows (one for x, one for y) are in
#tmp1 then you have a hit. Super simple ☺ .

Now in QA or SSMS do this.

declare @x int, @y int
set @x=2
set @y=3
if @x<=@y
print 'Yes'
else
print 'No'

No explanation necessary. Super simple. Now take the same
idea of comparing two integers and extend it to comparing
two tables.

if
table
     {
      row{'burns' as sName, 'x' as code}
      row{'burns' as sName, 'y' as code}
     }
       <=
         #tmp1
           print 'Yes'
                else 'No'
              
In other words, does each row for 'burns' occur in #tmp1?  
'Burns' can have codes in #tmp1 in addition to x and y (ie. 2<=3)
So 'burns' has to have at least a row for x and a row for y
in #tmp1. If in #tmp1 'burns' has only an 'x' or only a 'y'
no matter what other codes he has that's no good (2<=1).
In the case above you will see 'Yes' printed since the comparison
is true.

This whole scenario is referred to as relational division in
database terminology. But these simple ideas are obscured
by sql because you can't draw a picture of a row, nor a
table nor does sql understand comparing tables like integers.
So instead you're left with grouping and counting, joins,
intersects, existential queries and whatnot all trying to
express a simple idea yet at the same time obscuring it.

Now in a query you want to substitute all the unique names
from #tmp1 into our little table so for each person we can
test the comparison with #tmp1. What would such a query look
like?

select
 select distinct sName as aPerson from #tmp1 
  where      -- draw a table with two rows for each aPerson
             -- the 1st column has a value aPerson and
             -- the column is named 'sName'. The 2nd column is
             -- called 'code'. The column names and datatypes
             -- are the same as in #tmp1.
        table
             {
              row{aPerson as sName, 'x' as code},
              row{aPerson as sName, 'y' as code}
             }
             --  Compare the tables.
              <=
             --  Form a table from #tmp1 of rows belonging to the
             --  aPerson above.
              (Tmp1 where sName=aPerson);

Now this won't quite work in sql no matter where you execute it ☺ .
But what would a query really look like that will work with
#tmp1. Here it is. And it really is almost self-explanatory.
And it works in the D4 language! :)
(Tmp1 is a table same as #tmp1 stored in an Sql Server 2005 database).

select
  Tmp1 {sName aPerson}
    where
         table
              {
               row{aPerson sName, 'x' code},
               row{aPerson sName, 'y' code}
              }
               <=
              (Tmp1 where sName=aPerson);
   
aPerson
-------
burns 
jones 
smith 

Now this is what MS should be doing. Sql is a language of choice
for some things. But it certainly is not the choice language
for others.