Reverse IN Lookup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reverse IN Lookup

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-01 : 08:59:46

Brian writes "For my question, I have a database table that sort of looks like this in SQL Server 7.0 and under a Windows 2000 Advanced server platform:

AboveTable:

ID |  List
-----------
1  |  1,2,3
2  |  2
3  |  1,3,4

I want to be able to do the reverse of the SQL IN statement like below when I want to be able to query the value '2' against a row from AboveTable's List column:

SELECT ID
FROM AboveTable
WHERE 2 IN List

I know I can list out all the records in the table, then filter them via a programming language, but I would perfer to do all the work in SQL to save on processing time since the

How would I got about doing that?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 09:16:21

This would work:

SELECT ID FROM AboveTable
WHERE CharIndex(',2,', List)>0

Unfortunately you need to include the commas and you need to treat is as as string. If there are spaces between the digit and the commas you'll need to include them.

Can you change the table design (or add another table) so that the data is stored like this:

It'll be far more flexible and your queries will perform faster with a structure like this.

Edited by - robvolk on 03/01/2002 09:17:51

bandrzej
Starting Member

2 Posts

Posted - 2002-03-01 : 11:30:02

Ahh, but with your method, I would have to load the front and the back of the List column with commas. Is there any available SQL functions for list searching besides IN?

Unfortunately, the client created this database, and I have to work with the data structure they provided as part of the project specs. I could create a lookup table for each of the tables that have these list, however, they use these comma delimited list in all their tables.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 11:36:25

Search SQL Team for "CSV":

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Read the articles and see if any of them help you out.

Jay99

468 Posts

Posted - 2002-03-01 : 11:55:29

ok . . . so I am bored today at work . . .

create table abovetable (id int, list varchar(25))
insert into abovetable values(1,'1,2,3')
insert into abovetable values(2,'2')
insert into abovetable values(3,'1,2,4')
go

create table #array (id int identity(1,1), value varchar(25))
create table #results (id int)
declare @separator_position int,
@array_value varchar(25),
@array varchar(25),
@id int,
@lookforvalue varchar(25)

set @id = 1
set @lookforvalue = '4'

while @id <= (select max(id) from abovetable)
begin
delete
from #array

select @array = list + ','
from abovetable
where id = @id

while patindex('%,%', @array) <> 0
begin
select @separator_position = patindex('%,%', @array)
select @array_value = left(@array, @separator_position-1)
insert #array values(@array_value)
select @array = stuff(@array, 1, @separator_position, '')
end

if exists (select 1 from #array where value = @lookforvalue)
insert #results values (@id)

set @id = @id + 1
end

select * from #results
drop table #results
drop table #array

Jay

bandrzej
Starting Member

2 Posts

Posted - 2002-03-04 : 10:29:42

Jay,

the only problem i have about that method is the possibility of two people hitting the page at the same time, and those tables existing at the same time and giving wierd results. the page this query is used on will be only one off the homepage, so i know its gonna get hit alot.

thank you for the possible solution though!

-Brian

Jay99

468 Posts

Posted - 2002-03-04 : 11:33:32

quote:

...possibility of two people hitting the page at the same time, and those tables existing at the same time and giving wierd results....

From BOL

quote:

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from computers running Microsoft® SQL Server™. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

So, concurrent searches are not a problem.

Jay

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-04 : 15:50:33

Brian,

I am also apparently bored at work today so I will follow Robvolk's advise for you:

quote:

Search SQL Team for "CSV":

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Read the articles and see if any of them help you out.

It led me to this. Give it a try:


SELECT ID
FROM abovetable A CROSS JOIN Tally B 
WHERE Tally <= Len(',' + List + ',') 
 	AND CONVERT( INT , SubString(',' + List + ',' , 
			Tally , 
			CharIndex(',' , ',' + List + ',' , Tally) - Tally)  = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources