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]

sorry about the consugin info

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'.