Returning the Rows

Congratulations to Wangzhong Lu, database programmer at abridean in Halifax, Canada, and Hemant Sahgal, systems analyst for Iris Software in New Delhi, India. Wangzhong won first prize of $100 for the best solution to the April Reader Challenge, "Returning the Rows." Hemant won second prize of $50. Here’s a recap of the problem and the solution to the April Reader Challenge.

Problem

Alex is the programmer for a company that uses SQL Server 2000, 7.0, and 6.5. To find the customers who bought all the products of a specific group, he needs to write a query that asks whether rows of a particular type are the only rows in a table. The code has to work on all three SQL Server releases that the company is running. The following statement, which includes sample data, creates the table he’s querying:

CREATE TABLE #Test (
     i int,
     c varchar(10),
     PRIMARY KEY ( i, c )
     )
        INSERT INTO #Test VALUES( 1, 'Value #1' )
        INSERT INTO #Test VALUES( 1, 'Value #2' )
        INSERT INTO #Test VALUES( 1, 'Value #3' )
        INSERT INTO #Test VALUES( 2, 'Value #1' )
        INSERT INTO #Test VALUES( 2, 'Value #2' )
        INSERT INTO #Test VALUES( 3, 'Value #1' )

Given a set of values for column c, Alex needs to determine the rows that contain only and all those values for a particular value of i. For example, if Value #1 and Value #2 are the specified values for c, the query should return only rows where i equals 2. Similarly, if Value #1 is the specified value, the query should return only the row where i equals 3. Also, if Value #1, Value #2, and Value #3 are the specified values, the query should return only rows where i equals 1. Help Alex write an efficient query that returns the rows he seeks.

Solution

Alex can solve the problem by writing the following query:

DECLARE @Value1 varchar(10), @Value2 varchar(10)
SELECT @Value1 = 'Value #1'
              @Value2 = 'Value #2'
SELECT t.i
   FROM #Test AS t
GROUP BY t.i
HAVING SUM( CASE WHEN t.c IN ( @Value1, @Value2 ) THEN 1 ELSE -1 END ) = 2

In the query, Alex inserts the CASE expression into the SUM() function to obtain the count of rows that don’t match the values he’s searching for. If the count is two, Alex knows that the values he’s searching for are the only values in the table for a particular value of i.

Alex can also make this logic serve a more general purpose (i.e., check for any number of values). For example, he can use this logic to find SQL Server users who have been granted all permissions. (Here, each value denotes a permission, and each permission is stored in a normalized manner in the database.) In this case, Alex can use a temporary table to store the values that he’s seeking so that he can use them in the query. The following query uses a temporary table to make the logic more generic:

CREATE TABLE #Search ( c varchar(10) NOT NULL )
INSERT INTO #Search VALUES( 'Value #1' )
INSERT INTO #Search VALUES( 'Value #2' )
SELECT t.i
   FROM  #Test as t
   LEFT JOIN #Search As s
     ON s.Value = t.c
GROUP BY t.i
HAVING SUM( CASE s.value WHEN t.c THEN 1 ELSE -1 END ) = 
   ( SELECT count(*) FROM #Search )

The main difference between the query that uses the temporary search table and the query that contains variables is the join between the main table and the search table.

Now, test your SQL Server savvy in the May Reader Challenge, "Simplifying the Search" (below). Submit your solution in an email message to [email protected] by April 17. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Robert programs for a company that uses SQL Server 2000 as its database platform. One of the company’s database tables contains a list of names and other related columns. The following statement creates the table and its relevant columns and sample data:

CREATE TABLE Test (
    "ID" int PRIMARY KEY,
    "Name" varchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    "Description" varchar(2048) 
           COLLATE SQL_Latin1_General_CP1_CI_AS null
)
INSERT INTO Test ( "ID", "Name", "Description" )
VALUES( 1, 'This is a test name', 'Description for test name.' ) 
INSERT INTO Test ( "ID", "Name", "Description" )
VALUES ( 2, 'Long name' + replicate( '*', 400), 
           'Description for long name.' )
INSERT INTO Test ( "ID", "Name", "Description" )
VALUES ( 3, 'Another name', 'Description for another name.' )
INSERT INTO Test ( "ID", "Name", "Description" )
VALUES ( 4, 'Another Name', 'Description for another name.' )

Robert wants to optimize a query that is based primarily on the table’s Name column. Additionally, he wants to make the searches case sensitive only for this table and query. (All the searches that users perform on this table are equality searches.) Help Robert redesign the table and query so that he can efficiently achieve those two objectives.

Comments

Plain text