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

[southerncal]

0 results for
exterior
securing

for
white
silicone
sealant

1043 NULL NULL C
1043 NULL B NULL
1043 A B C

I am 2005 also

[NeoPa]

Thank you. That's very interesting. It tells me that the subquery appears to be working perfectly, and as expected. I will look more closely at how this is brought together when I get a sec and see what I discover.

PS. The 'white', 'silicone', & 'sealant' is the data I am working on for now. If we can manage to get that to work we can check other sets and include an attempt that uses fewer than three matches.

[NeoPa]

Could you also tell me what @Result resolved to during that test. Bearing in mind the results already posted, I'm struggling to see why 1043 would not have been returned from the whole SQL set. The data is as expected. What's not as I anticipate I wonder.

[southerncal]

?????? what @Result resolved to

Dont understand the question

[NeoPa]

OK. Let me see if I can clarify.

In the code you were running there is a local variable set up and used, called @Result (Line #12 of post #75). It is the value of this that I'm interested in.

To get this value follow these steps :

1. Add this line after line #16 in the SQL from post #75 :
   

   PRINT '@Result = "' + @Result + '"';

2. When you have rerun the test click on the Messages tab in SQL Server Management Studio. You will see the result (of printing @Result) in there and it will be copyable to paste in here in your new post.

[southerncal]

In the messages tab this is all that is shown

@Result = "ABC"

(3 row(s) affected)

[NeoPa]

That's quite as expected. What I still don't understand is why the individual pieces are all working perfectly, yet the stuff that puts them togather can't seem to work properly :S

Give me some time to put together another test that may take us forward again. It's not straightforward , as I don't see where it's failing.

[NeoPa]

Try this now, and post what you see. It now displays @Result in the grid so switching to Messages is no longer important :) I also changed it to handle NULL values before the concatenation rather than relying on SET CONCAT_NULL_YIE LDS_NULL OFF.

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

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

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

SELECT @Result AS [@Result];

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));

SELECT   PT_ProductID
       , ISNULL(MAX([MatchA]), '') + 
         ISNULL(MAX([MatchB]), '') + 
         ISNULL(MAX([MatchC]), '') AS [Comparator]
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  (ISNULL(MAX([MatchA]), '') + 
         ISNULL(MAX([MatchB]), '') + 
         ISNULL(MAX([MatchC]), '') = @Result);
GO

This code is a little longer, but much of it is for extra information. It's not anything we expect to end up using :)

[southerncal]

@result
ABC

1043 A B C

1043 ABC

[NeoPa]

If the last line is titled [T_ProductID] and [Comparator] then that indicates it's actually working properly, so try the following with your main data and say if it works :

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

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

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  (ISNULL(MAX([MatchA]), '') + 
         ISNULL(MAX([MatchB]), '') + 
         ISNULL(MAX([MatchC]), '') = @Result);
GO

[southerncal]

The results are very good and appear correct, no it is just the ordering issue.

I see that I can change one of your lines to
(PT_Type In('0','11','31 '))

What would I add so that it sort

if something is in 31
1st
if something is in 0
2nd
if something is in 11
3rd

and in each of these 3 groups by pt_productid

Thank you

[NeoPa]

As far as I understand it you can't. I haven't dealt with this earlier as it is very confusing trying to deal with multiple issues at the same time, and you didn't have the basic question sorted out until quite recently, let alone a viable answer. Now that it appears that part is out of the way we can put it aside and focus on why this part of the question doesn't make sense (ceratinly as you've described it so far).

If I have any details wrong then please tell me, but I'm working from what I understand you to have explained so far. I believe I have understood everything you've posted, so this should be accurate. Let's see.

[PT_Type] is a field that is associated with your records, but it is not a field that is aggregated upon. Unlike [PT_ProductID] (See the GROUP BY clause). As such, when the data is aggregated, [PT_Type] can only be accessed by using it within an aggregate function (Sum(); Max(); Avg(); Last(); etc). How could it possibly make sense otherwise? [PT_ProductID], because it is the same value, by definition, for each of the records within the aggregated group, is available to use. The value is predictable. [PT_Type] on the other hand, may well have various values for the individual records within the aggregated group, but what does the value of [PT_Type] mean when directed at the group itself (which is the resultant data you are now working with)? It can have no value of course. It is undefined. There is a Max([PT_Type]). There is a value usable for all of the aggregate functions, but direct access to a value for [PT_Type] itself makes no sense. Hence, I am unable to help you to sort by this value, that doesn't exist in logical reality.

PT_ProductID  PT_Type  PT_Value
12665            0     White
12665            0     Silicone
12665           11     Sealant
12734            0     White
12734           31     Silicone
12734           11     Sealant

Study the data above and you will see why sorting as you've suggested can make no logical sense.

[southerncal]

This might further confuse you.

Please let me clarify. Each pt_id in this table is a unique record.

Each record may not have a unique pt_productid in this table, and may have multiple entries.

The input would look at all pt_type's of 0, 11, and 31. Each of these records can have more than one word, so matching any of the words in each records is ok, thus the % %.

Each pt_productid can have multiple pt_types of the same number, and can have multiples of all 3 pt_types per pt_productid, the further definition that makes each one unique is pt_reference (even though this is not refered to in the query)

0 is top level
31 is middle level
11 is bottom level

For instance, if there are 3 matching pt_product's, then this is how the sorting would work best.

If there were two words searched for, and both words were matched for each of the 3 records, what ever word was matched in the highest level, would be sorted according to the above.


Thank you

[ck9663]

Sorry guys..it's getting more confusing... :)

I have not used your pdf as sample data...Too many to type. Can you attached a delimited text file as sample data, give some words that you are searching and the records that you want returned?

As I understand it, you want to combine all strings of the same pt_product_id, if all words exists somewhere on that string, that pt_product_id record is returned.

In this case, you will be combining the string regardless of pt_type. However, if one of the pt_type of those records with the same pt_product_id is equal to 31, that should be return first even if the text you are looking for is found in record with pt_type = '11'...

Did I even make sense ? :)


~~ CK