Limiting the amount of data used between two tables

Two tables: People and TestValues
People
--
Name varchar(50)
TestValues
--
TestString varchar(10)
Insert into People (Name) values ('William')
Insert into People (Name) values ('Blake')
Insert into People (Name) values ('Kevin')
Insert into People (Name) values ('Merry')
Insert into People (Name) values ('Gary')
Insert into People (Name) values ('Tamara')
Insert into People (Name) values ('Rich')
insert into TestValues (testString) values ('A')
insert into TestValues (testString) values ('M')
insert into TestValues (testString) values ('K')
insert into TestValues (testString) values ('X')
insert into TestValues (testString) values ('Z')
The goal is to have each table filter the other, so that we only return
those items that they have in common. So:
'William' has an "A", so WIlliam and A both are included (there is also
an M, but A is a match so M is not necessary)
"Blake" has an "A", so both are included
"Kevin" has a "K"
"Merry" matches "M"
"Gary" matches "A"
"Rich" doesn't match anything, and isn't included
"X" and "Z" don't match, and aren't included
I would rather not create two cursors and two temp tables if there is a
set based way to do this. Thanks!
- Wm in Kansas Cityselect p.name, min(t.teststring) as teststring
from people p
join testvalues t on p.name like '%'+t.teststring+'%'
group by p.name
this probably won't perform very well, especially if the amount of data
gets large.
seamlyne@.hotmail.com wrote:
> Two tables: People and TestValues
> People
> --
> Name varchar(50)
>
> TestValues
> --
> TestString varchar(10)
>
> Insert into People (Name) values ('William')
> Insert into People (Name) values ('Blake')
> Insert into People (Name) values ('Kevin')
> Insert into People (Name) values ('Merry')
> Insert into People (Name) values ('Gary')
> Insert into People (Name) values ('Tamara')
> Insert into People (Name) values ('Rich')
>
> insert into TestValues (testString) values ('A')
> insert into TestValues (testString) values ('M')
> insert into TestValues (testString) values ('K')
> insert into TestValues (testString) values ('X')
> insert into TestValues (testString) values ('Z')
> The goal is to have each table filter the other, so that we only return
> those items that they have in common. So:
> 'William' has an "A", so WIlliam and A both are included (there is also
> an M, but A is a match so M is not necessary)
> "Blake" has an "A", so both are included
> "Kevin" has a "K"
> "Merry" matches "M"
> "Gary" matches "A"
> "Rich" doesn't match anything, and isn't included
> "X" and "Z" don't match, and aren't included
> I would rather not create two cursors and two temp tables if there is a
> set based way to do this. Thanks!
> - Wm in Kansas City
>|||select * from #people join #TestValues on charindex(testString, Name)>0