Limiting the amount of data used between two tables

9 Dec 2005 9:18 PM

seamlyne

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

9 Dec 2005 9:31 PM

Trey Walpole

select 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.

seaml***@hotmail.com wrote:
Show quote

> 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
>

9 Dec 2005 9:47 PM

Alexander Kuznetsov

select * from #people join #TestValues on charindex(testString, Name)>0