search string for list of data 1

[Glowworm27]

I have a table of records that contain a field of descriptions of products.

I want to query the table descriptions, looking for a list of numbers. The numbers are contained in another table and I an only looking for certain numbers.

So lets say table 1 (productdescrptions) data contains

Rec_Id(integer), Description(varchar2)

1, 'part 123 a wheel'
2, 'part 234 a brakepad'
3, 'part 456 a muffler'
4, 'part 567 a bolt'

(the data is hypathetical but you get the idea)

table 2 (partnumbers) contains just part numbers, and part codes

Rec_Id(interger), PartNumber(varchar2), partcode(integer)

1, '123', 1
2, '234', 1
3, '456', 2
4, '567', 3



I want to return only records from table 1 (productdescription) where the description matches the Partnumber from table 2(partnumbers) with a partcode of 1

The list sql would look something like this
<Select Partnumber from Partnumbers where partcode =1>

and using the data above would return 2 records
'123','234'

and I want to find records in table 1(partdescription) where the descrpition may be a partial match to the list

such as
<Select * from Partdescriprion where description like '%123%'>
but use every item in the list returned from the table 2 query, '123', and '234'

I was thinking a cursor of sorts, but just want a sql i can execute to return the records.
I hope i didn't confuse anyone to badly

Peace

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Turkbear]

Hi,
Is the Description field in Table1 always that format? That is,the part number starts at the 6th position and is always 3 chars long, so that a
substr(Description,6,3)
would always return the actual part number?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

I wish

No the number can occur anywhere in the description field.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Glowworm27]

oh and just in case you ask, there can be more than one set of numbers in the description field. like for instance,

5, 'some tire 123 (R15 235/55)'

I understand if the query may return rows where the number may partialy match the other numbers, we can weed them out manualy but becasue our table 1 contains thousands and thousands of records we wanted to get a smaller list by matching them to part numbers from table 2

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Turkbear]

Hi,
Try going the other way

Select a.partnumber,b.description
from

partnumbers a,productdescriptions b

where
(
a.partcode = 1
 and
b.description like '%' ||a.PartNUmber || %
)

Cannot test , but see if that may give what you want..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

Turkbear,

You are the Man!

Thanks, that worked wonderfully, you missed the final quotes around the last % but I figured that out with no problem!

Peace Brother

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net