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
)

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

[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=1043 (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))

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