Joining tables

[ynnepztem]

Please help. I need a query that will retrieve Table2.Description and TABLE3.NAICS when the value in TABLE1.Word is selected. The problem is that TABLE1.BAD_IDS col contains several values to choose from. I am looking for just one to do a search on another table. I am using SQL Server 2000 and Windows NT 4.0.
I hope this isn't too confusing.

TABLE1:


(Word) (Bad_IDS) (Word_Count)
ABATEMENT 30733,31505,31506 3
ABATTOIR 2689 1
ACCOUNT 1877,1878,1879,1979,1980 27
ACCOUNTANT 26344,26347,26350,26351 14
ACCUMULATOR 10301 1

TABLE2:
(Bad_ID) (CONCORDANCE) (Description)
28226 2012 Hospice care services, in home
28227 2012 Nurse associations, visiting
28228 2012 Nursing agencies,primarily

TABLE3:
(Concordance) (NAICS)
1 11
2 11
3 11111
4 11112
5 11113
6 11114
7 11115
8 11115
9 11116
10 111191

 

[calidrake]

WHat should have happened is, you should make a seperate row for each code. So this...

(Word) (Bad_IDS)
ABATEMENT 30733,31505,31506

Should've been this...
(Word) (Bad_IDS)
ABATEMENT 30733
ABATEMENT 31505
ABATEMENT 31506

But here is a little SP that will return either a -1 (cannot find string) or a 1 (Found String). I think this is what you were looking for. If not just reply back.

CREATE PROCEDURE InCommaString (@SchString nvarchar(100), @FullString nvarchar(100), @RC int output)
AS
declare @end int
declare @Found int
declare @Length int
declare @ChopLength int
declare @CurrentString nvarchar(100)

set @Found = 0

while @Found = 0
begin
select @end = charindex(',',@FullString)
if @end = 0
begin
select @Found = -1
select @end = len(@FullString) + 1
end
else
select @CurrentString = ''
select @CurrentString = left(@FullString,(@end -1))
if @CurrentString = @SchString
select @found = 1
else if @Found <> -1
begin
select @Choplength = len(@FullString) - @End
select @FullString = right(@FullString,@ChopLength)
select @end = 0
end
end
set @RC = @Found
return


To use the Stored Procedure call it like this example....

declare @myRC int

(string from row)
Search String---\/ \/--FullString
execute InCommaString '30733','30733,31505,31506' ,
@RC = @myRC output
/ ||----Return Code

select @myRC