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

[southerncal]

in sql each pt_type can several different lines of information, each with a different pt_reference

so word 1 and 2 might be in the same row, but word 3 might be in a different row but have the same "d_id", but if all 3 values are matched, then I want to know the unique "d_id" for each unique matching result.

in the case below, 1941 and 2991 would be displayed

is it also to have an and/or type of excel statement? I noted in the query below where I need it.


THANK YOU


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

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
set @word1 = '%this%'
set @word2 = '%that%'
set @word3 = '%theother%'
select
productheader.ph_id,
productheader.ph_product
from productheader
inner join producttext
on productheader.ph_id = producttext.pt_productid
where (producttext.pt_type = '11' and producttext.pt_type = '0') 
--want and and/or type statement on the line right above
and 
(producttext.pt_value like 
@word1
and producttext.pt_value like 
@word2
and producttext.pt_value like 
@word3
)

[NeoPa]

This is really not clear. It's barely even a question at all.

Excuse me then if my guess as to what you are trying to ask is off, but if you want to identify all those [d_id] values that are referred to more than once in your data (as seems to be what you want) then try :

SELECT   [D_ID]
FROM     [YourData]
GROUP BY [D_ID]
HAVING   (COUNT(*) > 1)

Obviously, if this is not what you're after, asking the question clearly would be the next logical step. You will see if it makes sense when you read it back yourself before submitting it.

[southerncal]

Please let me clarify

Each pt_id in this table is a unique record.

Each pt_productid in this table may not be unique, and may have multiple entries

On the attached Word doc, there are several records with a pt_productid of 67811 and 5817

I need a query that will return any matching unique pt_productid that matches the one, two, or three word input.

The input would look at all pt_type's of 0 or 11

So in this case if I looked for

stiff elegant railing

it would return a pt_productid record of 5817

or a search for screen transaction hardware

it would return a pt_productid record of 67811

---------

side note: all three searched for words need to be matched, an option for finding two of the three words would also be good if something this complex is possible.

[NeoPa]

I think we're nearly there, but your explanation says one thing (you're after any ID where any of the search words are found) while your examples imply another (that all provided search words must be matched). It would help to know which of these you mean before attempting to help with some matching SQL.

[ck9663]

In your search for: stiff elegant railing

Why did you not return 23539 and 33720?

~~ CK

[southerncal]

in the match a partial, it would return the one item. Sorry to double talk.

to keep on the original one topic, it would be best to match all possible searched for terms.

so if searching for 3 words, looking in all fields, then return the unique productid if all 3 words are matched.

thank you

[NeoPa]

I'll try to look at this tomorrow. It'll probably involve result fields using CASE or IF() then checking the results match a string like '123' or something along those lines.

[southerncal]

Thank you for your replies

[NeoPa]

I'm assuming that you want the field names to reflect those in the attachment (PT_ID, PT_ProductID, PT_Type, PT_Reference & PT_Value) rather than those you posted (id, d_id, pt_type, pt_ref & pt_value), as there doesn't seem to be a match between them and I'd guess the attached list is less likely to contain human error.

Anyway, try this as a concept :

WITH cte AS
(
SELECT UNIQUE 
       PT_ProductID
     , CASE 
           WHEN PT_Value Like @Word1 THEN 'A'
           WHEN PT_Value Like @Word2 THEN 'B'
           WHEN PT_Value Like @Word3 THEN 'C'
       END AS [Match]
FROM   [YourData]
WHERE (PT_Value Like @Word1)
   OR (PT_Value Like @Word2)
   OR (PT_Value Like @Word3)
)
 
SELECT   PT_ProductID
FROM     cte
WHERE   (COUNT([Match])=3)
GROUP BY PT_ProductID

[southerncal]

I really wish I was better at SQL, I only know mid-level stuff. I am getting this error using the following.

If after this works, does the =3 matter if only 2 words are searched for?

Thank you

ERROR: Incorrect syntax near the keyword 'WITH'.
Server: Msg 147, Level 15, State 1, Line 25
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
---

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
set @word1 = '%test%'
set @word2 = '%search%'
set @word3 = '%%'
WITH cte AS
 (
 SELECT UNIQUE 
        PT_ProductID
        CASE 
            WHEN PT_Value Like @Word1 THEN 'A'
            WHEN PT_Value Like @Word2 THEN 'B'
            WHEN PT_Value Like @Word3 THEN 'C'
        END AS [Match]
 FROM   producttext
 WHERE (PT_Value Like @Word1)
    OR (PT_Value Like @Word2)
    OR (PT_Value Like @Word3)
 )
 
 SELECT   PT_ProductID
 FROM     cte
 WHERE   (COUNT([Match])=3)
 GROUP BY PT_ProductID

[southerncal]

BTW, if 3 words are entered all 3 must be match, if 2 words are entered, then both, etc.

((an option to match partial would be nice, but the utmost importance is on matching all words.))

Very Much Appreciated

[NeoPa]

My mistake. That was a nooby error. I should have used the HAVING clause for that instead of the WHERE clause. A bit of a Duh! moment.

As far as the changing requirements go of 3 or fewer words, which was introduced after the original question, that was not included in my previous submission. However, having given that some thought I will include it in this version as a bonus.

It should be something like :

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
                    WHEN @word2 THEN 1
                    WHEN @word3 THEN 2
                    ELSE 3
                END;
 
WITH cte AS
(
SELECT UNIQUE
       PT_ProductID
       CASE
           WHEN PT_Value Like @Word1 THEN 'A'
           WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
           WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
       END AS [Match]
FROM   [ProductText]
WHERE (PT_Value Like @Word1)
   OR (PT_Value Like @Word2)
   OR (PT_Value Like @Word3)
)
 
SELECT   PT_ProductID
FROM     cte
GROUP BY PT_ProductID
HAVING  (COUNT([Match]) = @NumWords);

[southerncal]

I can tell it is really close, and again thank you, but is WITH correct?

It is returning one error this time, and seems to be WITH this change

Server: Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'WITH'.

[NeoPa]

Maybe I needed to terminate the previous statement (Line #14). I've updated the previous post to reflect this.

[southerncal]

same error

Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'WITH'.

[NeoPa]

Why don't you post the SQL that the message is related to. I'm sure it's similar, but knowing exactly what's where may help to interpret what the error message is saying.

[southerncal]

same as above, but here goes

 declare @word1 varchar(501)
 declare @word2 varchar(501)
 declare @word3 varchar(501)
 DECLARE @NumWords int
 
 set @word1 = '%test%';
 set @word2 = '%search%';
 set @word3 = '';
 
 SET @NumWords = CASE ''
                     WHEN @word2 THEN 1
                     WHEN @word3 THEN 2
                     ELSE 3
                 END;
 
 WITH cte AS
 (
 SELECT UNIQUE
        PT_ProductID
        CASE
            WHEN PT_Value Like @Word1 THEN 'A'
            WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
            WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
        END AS [Match]
 FROM   [ProductText]
 WHERE (PT_Value Like @Word1)
    OR (PT_Value Like @Word2)
    OR (PT_Value Like @Word3)
 )
 
 SELECT   PT_ProductID
 FROM     cte
 GROUP BY PT_ProductID
 HAVING  (COUNT([Match]) = @NumWords);

[southerncal]

[ck9663]

I just checked the syntax, but did not read further...

1. Am not sure there's a UNIQUE keyword in MSSQL. If it's a column you need a comma after it. Or you mean DISTINCT.
2. There should be an equal sign between PT_ProductID and CASE.

Try it and let's see....

Happy Coding!!!

~~ CK

[southerncal]

It's SQL server. tried both unique and distinct also tried adding the = sign, all still the same results.

There is unique on w3 schools sql_unique.asp

All options return

Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'WITH'.

 declare @word1 varchar(501)
 declare @word2 varchar(501)
 declare @word3 varchar(501)
 DECLARE @NumWords int
 
 set @word1 = '%test%';
 set @word2 = '%search%';
 set @word3 = '';
 
 SET @NumWords = CASE ''
                     WHEN @word2 THEN 1
                     WHEN @word3 THEN 2
                     ELSE 3
                 END;
 
 WITH cte AS
 (
 SELECT unique
        PT_ProductID =
        CASE
            WHEN PT_Value Like @Word1 THEN 'A'
            WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
            WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
        END AS [Match]
 FROM   [ProductText]
 WHERE (PT_Value Like @Word1)
    OR (PT_Value Like @Word2)
    OR (PT_Value Like @Word3)
 )
 
 SELECT   PT_ProductID
 FROM     cte
 GROUP BY PT_ProductID
 HAVING  (COUNT([Match]) = @NumWords);

[southerncal]

All options return

Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'WITH'.

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
                    WHEN @word2 THEN 1
                    WHEN @word3 THEN 2
                    ELSE 3
                END;
 
WITH cte AS
(
SELECT unique
PT_ProductID =
CASE
    WHEN PT_Value Like @Word1 THEN 'A'
    WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
    WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
END AS [Match]
FROM   [ProductText]
WHERE (PT_Value Like @Word1)
OR (PT_Value Like @Word2)
OR (PT_Value Like @Word3)
)
 
SELECT   PT_ProductID
FROM     cte
GROUP BY PT_ProductID
HAVING  (COUNT([Match]) = @NumWords);

[ck9663]

Here's the corrected syntax. I did not check the logic, though...

/*
drop table [ProductText];
create table [ProductText]
(
   PT_Value varchar(10),
   PT_ProductID varchar(5)
)
*/
 
declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
WHEN @word2 THEN 1
WHEN @word3 THEN 2
ELSE 3
END;
 
WITH cte AS
(
SELECT 
PT_ProductID, 
CASE
WHEN PT_Value Like @Word1 THEN 'A'
WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
END AS [Match]
FROM [ProductText]
WHERE (PT_Value Like @Word1)
OR (PT_Value Like @Word2)
OR (PT_Value Like @Word3)
)
 
SELECT PT_ProductID
FROM cte
GROUP BY PT_ProductID
HAVING (COUNT([Match]) = @NumWords); 
 
 

Happy Coding!!!

~~ CK

[southerncal]

You dont know how much I wish I didnt have to reply with "not saying it worked", but same error.


Incorrect syntax near the keyword 'WITH'.

from the SQL coding just written by ck9663

[ck9663]

Are you sure you're running this is MS SQL Server? I just ran the code and it did not give me the same error you're getting. It did not return any result but it did not give me that error you're saying.

Or you're inserting this into another code?

~~ CK

[southerncal]

SQL server

In query analyzer, I just highlighted and ran what I am showing below, and received this error

Server: Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'WITH'.

 /*
 drop table [ProductTextx];
 create table [ProductTextx]
 (
    PT_Value varchar(10),
    PT_ProductID varchar(5)
 )
 */
 
declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
WHEN @word2 THEN 1
WHEN @word3 THEN 2
ELSE 3
END;
 
WITH cte AS
(
SELECT
PT_ProductID,
CASE
WHEN PT_Value Like @Word1 THEN 'A'
WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
END AS [Match]
FROM [ProductText]
WHERE (PT_Value Like @Word1)
OR (PT_Value Like @Word2)
OR (PT_Value Like @Word3)
)
 
SELECT PT_ProductID
FROM cte
GROUP BY PT_ProductID
HAVING (COUNT([Match]) = @NumWords);

[ck9663]

It looks like you're using earlier (than 2005) version of SQL Server...

Here, try this:

 
 
/*
drop table [ProductTextx];
create table [ProductTextx]
(
PT_Value varchar(10),
PT_ProductID varchar(5)
)
*/
 
declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
WHEN @word2 THEN 1
WHEN @word3 THEN 2
ELSE 3
END;
 
SELECT PT_ProductID
FROM 
(
SELECT
PT_ProductID,
CASE
WHEN PT_Value Like @Word1 THEN 'A'
WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
END AS [Match]
FROM [ProductText]
WHERE (PT_Value Like @Word1)
OR (PT_Value Like @Word2)
OR (PT_Value Like @Word3)
) cte
GROUP BY PT_ProductID
HAVING (COUNT([Match]) = @NumWords); 
 

Happy Coding!!!

~~ CK

[NeoPa]

That's something I've learned today. CTEs were introduced in version 2005. If you're using a server of an earlier version use SubQueries instead.

BTW. From my reading of CK's offering in post #26 it is an accurate reflection of the logic implemented in Pre-2005 T-SQL. The format's a bit strange for me to compare, but unless I overlooked something this should do the trick. My earlier offering, as picked up by CK earlier, omitted the comma (,) between lines #19 & #20 (as well as being written for SQL Server 2005 or later of course).

[southerncal]

Thank you VERY MUCH it actually runs without an error. The only issues now are

1. it is returning results on any of the entered searched for words

2. the declaration of it having to have a pt_type of 0 or 11 is no longer in the query?

that was my initial problem, i was trying to use the following which didnt work

where (producttext.pt_type = '11' and producttext.pt_type = '0')

***ideally pt_type of 0 would have a weight of xxxx and a pt_type of 11 would have a weight of zzzz


Thank you

[NeoPa]

1. I spoke too soon. I didn't notice the omission of the DISTINCT predicate from CK's suggestion. My original suggestion confused an Access property (Unique) with the SQL predicate DISTINCT, but it was a necessary part of the logic.
2. In post #10 you replaced my concept of [YourData] with producttext. This should have been something that related to the data you required rather than simply the table it originally came from.
   
   As this is actually the same table (which I didn't know at that time) it will be easier now for me to add the extra filtering at this level than to replace this table with a simple query that just filters the table with these values.

The resultant SQL should look something like :

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%search%';
set @word3 = '';
 
SET @NumWords = CASE ''
                    WHEN @word2 THEN 1
                    WHEN @word3 THEN 2
                    ELSE 3
                END;
 
SELECT   PT_ProductID
FROM     (
    SELECT DISTINCT
           PT_ProductID
         , CASE
               WHEN PT_Value Like @Word1 THEN 'A'
               WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
               WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
           END AS [Match]
    FROM   [ProductText]
    WHERE  (PT_Type In('0','11'))
      AND ((PT_Value Like @Word1)
       OR  (PT_Value Like @Word2)
       OR  (PT_Value Like @Word3))
    ) AS cte
GROUP BY PT_ProductID
HAVING  (COUNT([Match]) = @NumWords);

I'm assuming that PT_Type is a string field as indicated in your last post. If not then the quotes (') should be removed from the numbers in line #26.

[southerncal]

This is returning an error

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

declare*@word1*varchar(501)
declare*@word2*varchar(501)
declare*@word3*varchar(501)
DECLARE*@NumWords*int
 *
set*@word1*=*'%test%';
set*@word2*=*'%%';
set*@word3*=*'';
*
SET*@NumWords*=*CASE*''
 ********************WHEN*@word2*THEN*1
 ********************WHEN*@word3*THEN*2
 ********************ELSE*3
 ****************END;
 *
SELECT***PT_ProductID
FROM*****(
 ****SELECT*DISTINCT
 ***********PT_ProductID,
 ********* *CASE
 ***************WHEN*PT_Value*Like*@Word1*THEN*'A'
 ***************WHEN*(@NumWords*>*1)*AND*(PT_Value*Like*@Word2)*THEN*'B'
 ***************WHEN*(@NumWords*>*2)*AND*(PT_Value*Like*@Word3)*THEN*'C'
 ***********END*AS*[Match]
 ****FROM***[ProductText]
 ****WHERE**(PT_Type*In('0','11'))
 ******AND*((PT_Value*Like*@Word1)
 *******OR**(PT_Value*Like*@Word2)
 *******OR**(PT_Value*Like*@Word3))
 ****)*AS*cte
GROUP*BY*PT_ProductID
HAVING**(COUNT([Match])*=*@NumWords);

[southerncal]

Dont know where all the stars **** came from, they are not in the used query

[NeoPa]

Now I'm really confused.

Do you want me to tell you how you introduced the asterisks (*) into this SQL? I'm afraid I can't. I don't even know if these were introduced into your SQL or they're just in the post. Either way there's no point in my trying to work on this as it's not what we're interested in.

If you need to copy/paste reliably (from my post #29) then first click on Line Numbers, then on Select, then use Ctrl-C to copy. Pasting should be easier. Just use Ctrl-V.

BTW. There have been innumerable comments attached to your posts reminding you of the rule to post code only within code tags. I don't know why you seem to ignore these every time, but I must inform you that unless you show some attempt to conform to our rules I will be obliged to suspend your account. If this is simply confusion, then you would have done better to express this before now so that we could help and explain. The absence of any response at all is not appreciated.

[southerncal]

Sorry, it is totally my bad for the in between code thing, the *** occurred copying from query analyzer to this after pasting, again my fault, sorry.

Your last post produced this error

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '*'.


The last post from ck9663 partially worked (notes on what went wrong above)

Thank you

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%test%';
set @word2 = '%%';
set @word3 = '';
 
SET @NumWords = CASE ''
                     WHEN @word2 THEN 1
                     WHEN @word3 THEN 2
                     ELSE 3
                 END;
 
SELECT   PT_ProductID
FROM     (
     SELECT DISTINCT
            PT_ProductID,
            CASE
                WHEN PT_Value Like @Word1 THEN 'A'
                WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
                WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
            END AS [Match]
     FROM   [ProductText]
     WHERE  (PT_Type In('0','11'))
       AND ((PT_Value Like @Word1)
        OR  (PT_Value Like @Word2)
        OR  (PT_Value Like @Word3))
     ) AS cte
GROUP BY PT_ProductID
HAVING  (COUNT([Match]) = @NumWords);

[southerncal]

sorry again about the ****** I went from query analyzer where there were no stars, to note pad no stars, to pasting in this message box and still no stars ****

after I click "Post Quick Reply" it adds the stars???

I am sorry, I really am not doing this intentionally.

[NeoPa]

I fixed the code tags, but I notice you tried. As there's still some confusion with this I'll copy something in that may help.

Tags (generally) are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [code] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page). You must select the code in your post, and hit the button on the tool bar which looks like a hash (#). This will automatically format the post such that the [code] tags surround the code you're posting. This will display the code in a Code Box, quite differently from the standard formatting of a post.

[NeoPa]

SouthernCal:
sorry again about the ****** I went from query analyzer where there were no stars, to note pad no stars, to pasting in this message box and still no stars ****

after I click "Post Quick Reply" it adds the stars???

I am sorry, I really am not doing this intentionally.

This is helpful information. I have no problem with your failing to get rid of these stars at this point. Frankly, you've already shown good sense in doing this via NotePad. That's good thinking.

Unfortunately :

1. I think this may be a Unicode issue, so getting this converted to standard ASCII text is important, not only for viewing clearly in the thread, but also for running in QA.
2. Notepad is now a bit clever and recognizes Unicode, so wont convert it to standard ASCII characters automatically for you, as is required.

I have no idea what is causing the conversion in the first place, but I suspect this is getting into the QA version somehow else how would the error message refer to it.

I use TextPad to convert Unicode to ASCII, but that's 3rd party software (You can download a free trial if you like of course).

BTW I just updated your post again to remove the asterisks (*) but the error message still refers to it of course.

[southerncal]

Thank you thank you thank you, this now works perfect!!!

If I were to ask something over and above the original request which is now complete, do I resubmit a question, or add it here?

on my post 3 hours ago, after the **** re the weighting of pt_type's 0 and 11

[southerncal]

I just noticed something strange, if both words are in field 0 (field 0 is a title and contains more than one word, thus the %% marks in the SET word = '%%'), that when both words are in this field it doesnt find this as a result.

Besides the possible pt_type 0 issue, when I change the order of words to look for, often I get different results, either way the results should be the same, just in a different order if this is able to order according to a point value.

And if just one word is entered, it finds no results

Thank you Very Much

[NeoPa]

SouthernCal:
on my post 3 hours ago, after the **** re the weighting of pt_type's 0 and 11

What's the post #? I don't see what you're referring to.

PS. The SQL already handles filtering only those records where PT_Type is either '0' or '11' as per your example. I don't ever recall seeing anything else requested (and I've checked through your posts again of course).

[NeoPa]

SouthernCal:
And if just one word is entered, it finds no results

How do you declare that in your SQL?

If you do it as in your earlier SQL (Post #33) then that's not right. @Word3 is '' but @Word2 is '%%'. I left @Word3 as an example of how to do it properly. Try it using the empty string (as with @Word3) and let me know how you get on.

[southerncal]

On the below, both words are entered in the pt_type = '0' field, both under the same pt_productid.

When I run this it finds no results, both words are there, both in pt_type = '0'

Besides this small glitch, the other is when I search for XXXXXX TTTTTT it returns different results than when I search for TTTTTT XXXXXX

The weighting I was hoping to add was to change the order of the results shown. If a words was found in field 0 then it would have a higher value than if the word was found in 11. Say two words were searched for, and there were 10 results, some of the results would have both words found in field 0, some with one word in 0 and one in 11, the rest with both words in field 11.

The top of the list would be the 0,0's next would be 0,11's, and the balance would be 11,11's.

(then take those results and order by pt_productid

This is the last code that is working but with a few glitches.

Thank you

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @NumWords int
 
set @word1 = '%exterior%';
set @word2 = '%securing%';
set @word3 = '';
 
SET @NumWords = CASE ''
                    WHEN @word2 THEN 1
                    WHEN @word3 THEN 2
                    ELSE 3
                END;
 
SELECT   PT_ProductID
FROM     (
    SELECT DISTINCT
           PT_ProductID,
           CASE
               WHEN PT_Value Like @Word1 THEN 'A'
               WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
               WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
           END AS [Match]
    FROM   [ProductText]
    WHERE  (PT_Type In('0','11'))
      AND ((PT_Value Like @Word1)
       OR  (PT_Value Like @Word2)
       OR  (PT_Value Like @Word3))
    ) AS cte
GROUP BY PT_ProductID
HAVING  (COUNT([Match]) = @NumWords);

[ck9663]

Sorry guys, I got lost somewhere :)

Could you post some sample data again and how you want the result should look like? Give some good distribution of samples, even if it's just a few records...

~~ CK

[southerncal]

Example attached, the last query above sort of worked, just the couple of outstanding issues as noted in POST 41.


Thank you

[ck9663]

Given these 23 records, could you give me some samples of words you're searching and which records should be returned?

~~ CK

[southerncal]

search for

steel
hardware

would return 12357 12358 12359
-------------
search for

g2766
packaged

would return 12360
-------------
search for

abrasiv
hand
pad

would return 12360
-------------

if in the first sample above
steel
hardware

if one of the results had the word hardware as pt_type 0

it would move that result to the top of the list because ideally pt_type = 0
is worth more than pt_type 11

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