need sql to condense request and add an and/or statement

[ck9663]

Is your input a single text string separated by space or two text ?

On your sample:

search for

g2766
packaged

would return 12360

I thought you said if 2 words were given, the two should be found on the pt_value string. In this case, no string contains both text that you are searching. Is it an AND (inclusive, all text should be found in the pt_value string)? Is it an OR (exclusive, if one of the text is found in the pt_value string, the record should be returned).


~~ CK

[NeoPa]

Input is as per lines #6 through #8 of post #41.

[southerncal]

Correct as per NeoPa, search for two or three words in any order and you get the same results.

Someone would do a search, just like in a search box on a web site, so it would be an "and"

[ck9663]

I'm a little confused.... There are no pt_value with the text 'Steel' and 'Hardware' yet in your sample, you search for

steel
hardware

would return 12357 12358 12359

Let me try it the other way...If you combine all pt_value of 12357, the word 'steel' and 'hardware' can be found there somewhere that's why you return it...Same is true if you combine all pt_value for 12358...

Is that what you are trying to do?


~~ CK

[southerncal]

did you view the latest example.pdf added to post #43

In the first line of the data there is a word called steel

CRL 7/16" steel sliding win......

In certain PT_value's there is more than one word, pt_type's of 0 have more than one word, thus the using of %%

[ck9663]

Will this work:

sample data population..

declare @YourTable as table
(
   pt_ID int,
   pt_productID int,
   pt_Type smallint,
   pt_Reference smallint,
   pt_Value varchar(500)      
)

declare @word1 varchar (500), @word2 varchar (500), @word3 varchar (500)

insert into @YourTable
select 2960882, 12357, 0, 1, 'CLR 7/16" Steel Sliding Window'
union all
select 4741884, 12357, 11, 1, 'window'
union all
select 3278764, 69765, 11, 999, 'J0SB'
union all
select 4658482, 67811, 11, 8, 'screen'
union all
select 2892238, 47499, 0, 1, 'CRL Custom Finish P7 Series 36" End Post Swivel Fitting Railing Kit'
union all
select 4069940, 1941, 11, 4, 'this'
union all
select 2793773, 8756, 0, 1, 'CRL Black Die Cast Sliding Screen Door Pull With 2-3 8" Screw Holes'
union all
select 2892239, 47500, 0, 1, 'CRL Custom Finish P7 Series 36" Corner Post Swivel Fitting Railing Kit'
union all
select 4161303, 14657, 11, 1, 'TRANSACTION'
union all
select 4176252, 29539, 11, 2, 'railing'
union all
select 4184049, 1941, 11, 9, 'that'
union all
select 4188079, 33720, 11, 3, 'railing'
union all
select 2762021, 1941, 0, 1, 'theother'
union all
select 2762022, 34055, 1, 1, 'C1DP42BRZ_34055.gif'
union all
select 3254121, 3236, 5, 1, 'Face Diameter::2 in (50.8 mm)'
union all
select 5001800, 31146, 16, 1, 'CRL Abrazadera de manguito a presión para cristal, 90 grados, en cobre cepillado antiguo'
union all
select 2762024, 2991, 1, 2, 'this is a test'
union all
select 4191045, 2991, 11, 2, 'and that is two'
union all
select 2794393, 2991, 1, 1, 'other one'
union all
select 2960910, 12359, 0, 1, 'CLR 1/2" Steel Sliding Window'

T-SQL

set @word1 = '%window%';
set @word2 = '';
set @word3 = '';

;with pt0
as(
select pt_productid 
from @YourTable
where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type = 0
group by pt_productid 
)
,
pt1
as (
select pt_productid
from @YourTable
where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type <> 0
group by pt_productid 
)
select pt0.pt_productid from pt0
union all
select pt1.pt_productid from pt1
where pt1.pt_productid not in (select pt0.pt_productid from pt0)

Good Luck!!!

~~ CK

[southerncal]

Correct. If one, two or three words are searched, then if those 3 words are anywhere in the combined records of any pt_productid either all somewhere on one line, or in any combination of pt_productid lines, then that record is returned.

Then it continues to search for the same condition.

If searching for say two words

If one pt_productid has one word that is a pt_type of 0 and one word is a pt_type of 11. and there is another pt_productid that has both words as pt_type 11 that result would be after the first

Likewise if a pt_productid had both words as a pt_type of 0, that record would be above

pt_type = 0 higher ranking than pt_type = 11

(if matching values, then order by pt_productid)

Thank you

[southerncal]

Unfort it says
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '*'.

If I run with or without declare message, it error's here
set*@word1*=*'% window%';

(looks like there is an error i dont see)

see attached

[ck9663]

Sorry, I forgot CTE is not working on your side...

Try this

select pt0.pt_productid 
from 
(
   select pt_productid 
   from @YourTable
   where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type = 0
   group by pt_productid 
) pt0
union all
select pt1.pt_productid 
from 
(
   select pt_productid
   from @YourTable
   where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type <> 0
   group by pt_productid 
)pt1
where pt1.pt_productid not in 
   (select pt3.pt_productid 
    from 
    (
      select pt_productid 
      from @YourTable
      where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type = 0
      group by pt_productid     
    ) pt3   
   )

Good Luck!!!


~~ CK

[southerncal]

I am sure I am killing you on this!

Same error Line 1: Incorrect syntax near '*'.

set*@word1*=*'% window%';

~~running SQL server 2005 if that matters

[ck9663]

I don't have any asterisk on my code. Why is it showing in yours?

~~ CK

[southerncal]

It is a copy and paste thing, there is not *** in the actual SQL, look at the image on my last post.

See if this snippet works from notepad


declare @word3 varchar(501)
set @word1 = '%window%';
set @word2 = '';
set @word3 = '';
select pt0.pt_producti d
from

[NeoPa]

From your posted pic it seems clear that one of your quote characters is not being treated as such. Maybe it's a slanted quote from a word processor or something. The red in the code says that it is treating the rest of your whole procedure as a string literal that starts at the point the string should be terminated (at the end of the fourth line).

[southerncal]

The stars were actually tabs or spaces from the copy and paste from the browser, to SQL, then back.

When I removed all spacing on SQL, the query worked!!!
:-) exactly as requested.

What is the dif between pt0 and pt3

[ck9663]

Nothing. The point is to eliminate those records that's already in pt0. Your requirement says you only need the pt_id once, not repeating. It was named pt3 to identify it from pt0.

Happy Coding!!!

~~ CK