wades wades - 1 month ago 10
SQL Question

How to select rows where a combination of columns matches a list of values?

Suppose I have a table with values like this:

CREATE TABLE foo
(
ID int NOT NULL,
Box1 varchar(50),
Box2 varchar(50),
Box3 varchar(50),
CONSTRAINT PK_foo PRIMARY KEY (ID)
);

INSERT INTO foo (ID, Box1, Box2, Box3) VALUES
(0, 'FOOBIE BLETCH', NULL, NULL),
(1, 'DUAM XNAHT', NULL, NULL),
(2, 'HACKEM MUCHE', 'FNORD', NULL),
(3, 'DAIYEN FOOELS', 'ELBIB YLOH', 'GARVEN DEH'),
(4, 'JUYED AWK YACC', 'FNORD', NULL),
(5, 'FOOBIE BLETCH', NULL, NULL),
(6, 'JUYED AWK YACC', 'FOOBIE BLETCH', NULL),
(7, 'HACKEM MUCHE', 'FNORD', 'FOOBIE BLETCH'),
(8, 'DAIYEN FOOELS', 'GARVEN DEH', 'ELBIB YLOH')


How do I find values of
foo.ID
where any combination of
Box1
,
Box2
, and
Box3
contain specified values? The order of the values to be found does not matter. Rows that contain more values than the ones being looked for should appear in the results. e.g.

DECLARE @ArgValue varchar

SET @ArgValue = 'FOOBIE BLETCH' -- match 0, 5, 6, 7
SET @ArgValue = 'GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH' -- match 3, 8
SET @ArgValue = 'FNORD, JUYED AWK YACC' -- match 4


Assume (if necessary) the existence of a stored proc
ArgVal_Split(@ArgVal varchar(max), @Delimiter char(1))
that can extract the substrings and return them as a single-column table.

My real data is actually much more complicated than this (one table has 20 different columns that might match) so I'm looking for solutions that don't involve enumerating the column names.

Answer
-- uncomment the 'create table' commands the first time you run it, and then comment them out again
-- requires costest_split, with fixes

---- this table stands for CostEst_ClaimInfo
CREATE TABLE foo 
(
  ID int NOT NULL,
  Box1 varchar(50),
  Box2 varchar(50),
  Box3 varchar(50),
  CONSTRAINT PK_foo PRIMARY KEY (ID)
);

---- This table stands for CostEst_ClaimDiagnoses
create table FooCrossRef
(
    ID int NOT NULL,
    BoxVal varchar(50)
)

---- This table stands for #tdiags
create table FooVals ( ABoxVal varchar(50) )

---- This table stands for #tClaims
CREATE TABLE fooResults
(
  ID int NOT NULL,
  Box1 varchar(50),
  Box2 varchar(50),
  Box3 varchar(50),
  CONSTRAINT PK_fooRes PRIMARY KEY (ID)
);

-- empty out all the tables every time, for simplicity
delete from foo
delete from FooCrossRef
delete from FooVals
delete from fooResults


declare @BoxCount int

INSERT INTO foo (ID, Box1, Box2, Box3) VALUES 
  (0, 'FOOBIE BLETCH', NULL, NULL),
  (1, 'DUAM XNAHT', NULL, NULL),
  (2, 'HACKEM MUCHE', 'FNORD', NULL),
  (3, 'DAIYEN FOOELS', 'ELBIB YLOH', 'GARVEN DEH'),
  (4, 'JUYED AWK YACC', 'FNORD', NULL),
  (5, 'FOOBIE BLETCH', NULL, NULL),
  (6, 'JUYED AWK YACC', 'FOOBIE BLETCH', NULL),
  (7, 'HACKEM MUCHE', 'FNORD', 'FOOBIE BLETCH'),
  (8, 'DAIYEN FOOELS', 'GARVEN DEH', 'ELBIB YLOH')
  ,(9, 'JUYED AWK YACC', 'GARVEN DEH', 'ELBIB YLOH')
  ,(10, 'ELBIB YLOH', NULL, NULL)
  ,(11, 'JUYED AWK YACC', 'FOOBIE BLETCH', 'FNORD')

DECLARE @ArgValue varchar(max)

-- unit-test arg values
--SET @ArgValue = 'FOOBIE BLETCH' -- match 0, 5, 6, 7, 11
--SET @ArgValue = 'GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH' -- match 3, 8
--SET @ArgValue = 'FNORD, JUYED AWK YACC' -- match 4, 11
SET @ArgValue = 'FOOBIE BLETCH, LEP GEX VEN ZEA' -- match nothing

insert into FooCrossRef (ID, BoxVal )
    select ID, Box
    from
    (
        select foo.ID, Box1, Box2, Box3 from foo
    ) as PVT
    UNPIVOT (Box for position in (Box1, Box2, Box3)) as UnPvt



-- Do the thing with the argument
insert into FooVals exec costest_split @OrigString = @ArgValue, @Delimiter = ',';

set @BoxCount = (select Count(distinct ABoxVal) from FooVals)



insert into fooResults (ID, Box1, Box2, Box3)
(

    select distinct foo.ID, Box1, Box2, Box3  from foo 
        join FooCrossRef on foo.ID = FooCrossRef.ID 
        join FooVals on FooCrossRef.BoxVal = FooVals.ABoxVal
    group by foo.ID, Box1, Box2, Box3
    having count(distinct FooCrossRef.BoxVal) >=  @BoxCount
)

select * from fooResults
Comments