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

[southerncal]

Actually I spoke too soon.

Just tried all different conditions, and if both (all) words are pt_type = 11 it returns no results.

(and when the same word is in a pt_type = 0 and pt_type = 11 it moves it to the bottom half of the list)

Side note: since a tweak is needed, can there be a pt_type = 31 added which is higher priority in the listing than 0

so first 31 then 0 then 11


thanks again

[ck9663]

So, has this been answered? There's already a chosen answer and yet I saw a question as last post...

So-Cal, is this done?

~~ CK

[southerncal]

it was but after all testing for all conditions, there were some glitches, so not totally working.

[ck9663]

Based on my sample data, could you give me a word that's giving you the problem...

and when the same word is in a pt_type = 0 and pt_type = 11 it moves it to the bottom half of the list

~~ CK

[southerncal]

using the query from post 54

and the data from example.pdf from POST 43

if i search for

set @word1 = 'steel';
set @word2 = 'sliding';
set @word3 = 'hardware';

nothing is returned

[ck9663]

And it should've returned what?


~~ CK

[southerncal]

12357
12359

(and 12358 although you cant see the word SLIDING on that record on the PDF)

[NeoPa]

I admit I don't get the bit about the weighting of PT_Types. Surely the values of PT_Type could vary across a group (unless they can't and you haven't told us this, but that would be a big thing to miss out).

Anyway, if it helps (and you may decide that CK's solutions are preferable. I know I've let this run for a while thinking it was fully covered already) then good. Otherwise no probs. I expect CK has you covered anyway.

For what it's worth though, this should handle the scenarios where more than one of your search items are found within the same record. That was never designed for before, as it wasn't made clear it was required until later. Anyway, see if this is any help :

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @Result varchar(3)

set @word1 = '%exterior%';
set @word2 = '%securing%';
set @word3 = '';

SET @Result = CASE ''
                  WHEN @word2 THEN 'A'
                  WHEN @word3 THEN 'AB'
                  ELSE 'ABC'
              END;

SELECT   PT_ProductID
FROM     (
    SELECT DISTINCT
           PT_ProductID
         , CASE
               WHEN PT_Value LIKE @Word1 THEN 'A'
               ELSE NULL
           END AS [MatchA]
         , CASE
               WHEN (@Word2 > '') AND (PT_Value LIKE @Word2) THEN 'B'
               ELSE NULL
           END AS [MatchB]
         , CASE
               WHEN (@Word3 > '') AND (PT_Value LIKE @Word3) THEN 'C'
               ELSE NULL
           END AS [MatchC]
    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  (MAX([MatchA]) + 
         MAX([MatchB]) + 
         MAX([MatchC]) = @Result);

[southerncal]

Thank you for your assistance.

I ran this using a set of different words.

set @word1 = '%white%';
set @word2 = '%silicone%';
set @word3 = '%sealant%';

The image link (Attached) is one of many records that contain each of the words in one or more records, for this same pt_productid.

Unfortunately the query produced 0 results.

[NeoPa]

That looks like a string manipulation issue. 'A' + 'B' + NULL resolves to NULL and not to 'AB'. Let me have another look and I'll come back to you.

[NeoPa]

Please test what happens if you add the following line in before line #10

SET CONCAT_NULL_YIELDS_NULL OFF;

This is session level and just a test. It is not good practice to use this, but it will tell us if we're on the right track.

[southerncal]

It returns 2 records, but not this record, while ignoring the 3rd word

[NeoPa]

I'm not clear on exactly what you're saying here. Is the data still the data from the attachment in post #69? From that I would only expect one possible record returned, or are you saying there is much more data (that I know nothing of) but that it returns nothing for PT_ProductID=10 43 (the detailed data of which we know all about)?

PS. It's a shame sending databases is not as easy as with Access (generally). I'd love to get my hands on this and see what it is that I'm not understanding clearly here.

[southerncal]

Yes, that data is still there, in this subset there are a lot more records.

I was just saying that it did return valid data, just not complete / correct results.

It should of but didnt return record 1043 as it should of

[NeoPa]

I'm not clear what's happening here as I tested MAX() with values that included NULLs and without and it behaved as expectedly. I'm using version 2005.

Perhaps you could run another dummy test for me and post just the records returned for 1043 :

declare @word1 varchar(501)
declare @word2 varchar(501)
declare @word3 varchar(501)
DECLARE @Result varchar(3)
 
set @word1 = '%exterior%';
set @word2 = '%securing%';
set @word3 = '';
 
SET CONCAT_NULL_YIELDS_NULL OFF;

SET @Result = CASE ''
                  WHEN @word2 THEN 'A'
                  WHEN @word3 THEN 'AB'
                  ELSE 'ABC'
              END;
 
SELECT DISTINCT
       PT_ProductID
     , CASE
           WHEN PT_Value LIKE @Word1 THEN 'A'
           ELSE NULL
       END AS [MatchA]
     , CASE
           WHEN (@Word2 > '') AND (PT_Value LIKE @Word2) THEN 'B'
           ELSE NULL
       END AS [MatchB]
     , CASE
           WHEN (@Word3 > '') AND (PT_Value LIKE @Word3) THEN 'C'
           ELSE NULL
       END AS [MatchC]
FROM   [ProductText]
WHERE  ([PT_ProductID] = 1043)
  AND  (PT_Type In('0','11'))
  AND ((PT_Value LIKE @Word1)
   OR  (PT_Value LIKE @Word2)
   OR  (PT_Value LIKE @Word3))