468,321 Members | 1,820 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,321 developers. It's quick & easy.

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

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



Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. set @word1 = '%this%'
  5. set @word2 = '%that%'
  6. set @word3 = '%theother%'
  7. select
  8. productheader.ph_id,
  9. productheader.ph_product
  10. from productheader
  11. inner join producttext
  12. on productheader.ph_id = producttext.pt_productid
  13. where (producttext.pt_type = '11' and producttext.pt_type = '0') 
  14. --want and and/or type statement on the line right above
  15. and 
  16. (producttext.pt_value like 
  17. @word1
  18. and producttext.pt_value like 
  19. @word2
  20. and producttext.pt_value like 
  21. @word3
  22. )
Sep 3 '10 #1

✓ answered by ck9663

Sorry, I forgot CTE is not working on your side...

Try this

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. select pt0.pt_productid 
  4. from 
  5. (
  6.    select pt_productid 
  7.    from @YourTable
  8.    where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type = 0
  9.    group by pt_productid 
  10. ) pt0
  11. union all
  12. select pt1.pt_productid 
  13. from 
  14. (
  15.    select pt_productid
  16.    from @YourTable
  17.    where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type <> 0
  18.    group by pt_productid 
  19. )pt1
  20. where pt1.pt_productid not in 
  21.    (select pt3.pt_productid 
  22.     from 
  23.     (
  24.       select pt_productid 
  25.       from @YourTable
  26.       where pt_value like '%' + @word1 + '%' + @word2 + '%' + @word3 and pt_type = 0
  27.       group by pt_productid     
  28.     ) pt3   
  29.    )
  30.  
  31.  
Good Luck!!!


~~ CK

88 2935
NeoPa
32,081 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [D_ID]
  2. FROM     [YourData]
  3. GROUP BY [D_ID]
  4. 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.
Sep 6 '10 #2
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.
Attached Images
File Type: jpg sql example.jpg (87.8 KB, 167 views)
Sep 7 '10 #3
NeoPa
32,081 Expert Mod 16PB
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.
Sep 7 '10 #4
ck9663
2,878 Expert 2GB
In your search for: stiff elegant railing

Why did you not return 23539 and 33720?

~~ CK
Sep 7 '10 #5
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
Sep 7 '10 #6
NeoPa
32,081 Expert Mod 16PB
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.
Sep 7 '10 #7
Thank you for your replies
Sep 7 '10 #8
NeoPa
32,081 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. WITH cte AS
  2. (
  3. SELECT UNIQUE 
  4.        PT_ProductID
  5.      , CASE 
  6.            WHEN PT_Value Like @Word1 THEN 'A'
  7.            WHEN PT_Value Like @Word2 THEN 'B'
  8.            WHEN PT_Value Like @Word3 THEN 'C'
  9.        END AS [Match]
  10. FROM   [YourData]
  11. WHERE (PT_Value Like @Word1)
  12.    OR (PT_Value Like @Word2)
  13.    OR (PT_Value Like @Word3)
  14. )
  15.  
  16. SELECT   PT_ProductID
  17. FROM     cte
  18. WHERE   (COUNT([Match])=3)
  19. GROUP BY PT_ProductID
Sep 8 '10 #9
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.
---

Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. set @word1 = '%test%'
  5. set @word2 = '%search%'
  6. set @word3 = '%%'
  7. WITH cte AS
  8.  (
  9.  SELECT UNIQUE 
  10.         PT_ProductID
  11.         CASE 
  12.             WHEN PT_Value Like @Word1 THEN 'A'
  13.             WHEN PT_Value Like @Word2 THEN 'B'
  14.             WHEN PT_Value Like @Word3 THEN 'C'
  15.         END AS [Match]
  16.  FROM   producttext
  17.  WHERE (PT_Value Like @Word1)
  18.     OR (PT_Value Like @Word2)
  19.     OR (PT_Value Like @Word3)
  20.  )
  21.  
  22.  SELECT   PT_ProductID
  23.  FROM     cte
  24.  WHERE   (COUNT([Match])=3)
  25.  GROUP BY PT_ProductID
Sep 8 '10 #10
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
Sep 8 '10 #11
NeoPa
32,081 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. DECLARE @NumWords int
  5.  
  6. set @word1 = '%test%';
  7. set @word2 = '%search%';
  8. set @word3 = '';
  9.  
  10. SET @NumWords = CASE ''
  11.                     WHEN @word2 THEN 1
  12.                     WHEN @word3 THEN 2
  13.                     ELSE 3
  14.                 END;
  15.  
  16. WITH cte AS
  17. (
  18. SELECT UNIQUE
  19.        PT_ProductID
  20.        CASE
  21.            WHEN PT_Value Like @Word1 THEN 'A'
  22.            WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.            WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.        END AS [Match]
  25. FROM   [ProductText]
  26. WHERE (PT_Value Like @Word1)
  27.    OR (PT_Value Like @Word2)
  28.    OR (PT_Value Like @Word3)
  29. )
  30.  
  31. SELECT   PT_ProductID
  32. FROM     cte
  33. GROUP BY PT_ProductID
  34. HAVING  (COUNT([Match]) = @NumWords);
Sep 8 '10 #12
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'.
Sep 9 '10 #13
NeoPa
32,081 Expert Mod 16PB
Maybe I needed to terminate the previous statement (Line #14). I've updated the previous post to reflect this.
Sep 9 '10 #14
same error

Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'WITH'.
Sep 9 '10 #15
NeoPa
32,081 Expert Mod 16PB
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.
Sep 9 '10 #16
same as above, but here goes

Expand|Select|Wrap|Line Numbers
  1.  declare @word1 varchar(501)
  2.  declare @word2 varchar(501)
  3.  declare @word3 varchar(501)
  4.  DECLARE @NumWords int
  5.  
  6.  set @word1 = '%test%';
  7.  set @word2 = '%search%';
  8.  set @word3 = '';
  9.  
  10.  SET @NumWords = CASE ''
  11.                      WHEN @word2 THEN 1
  12.                      WHEN @word3 THEN 2
  13.                      ELSE 3
  14.                  END;
  15.  
  16.  WITH cte AS
  17.  (
  18.  SELECT UNIQUE
  19.         PT_ProductID
  20.         CASE
  21.             WHEN PT_Value Like @Word1 THEN 'A'
  22.             WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.             WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.         END AS [Match]
  25.  FROM   [ProductText]
  26.  WHERE (PT_Value Like @Word1)
  27.     OR (PT_Value Like @Word2)
  28.     OR (PT_Value Like @Word3)
  29.  )
  30.  
  31.  SELECT   PT_ProductID
  32.  FROM     cte
  33.  GROUP BY PT_ProductID
  34.  HAVING  (COUNT([Match]) = @NumWords);
Sep 9 '10 #17

Sep 9 '10 #18
ck9663
2,878 Expert 2GB
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
Sep 9 '10 #19
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'.


Expand|Select|Wrap|Line Numbers
  1.  declare @word1 varchar(501)
  2.  declare @word2 varchar(501)
  3.  declare @word3 varchar(501)
  4.  DECLARE @NumWords int
  5.  
  6.  set @word1 = '%test%';
  7.  set @word2 = '%search%';
  8.  set @word3 = '';
  9.  
  10.  SET @NumWords = CASE ''
  11.                      WHEN @word2 THEN 1
  12.                      WHEN @word3 THEN 2
  13.                      ELSE 3
  14.                  END;
  15.  
  16.  WITH cte AS
  17.  (
  18.  SELECT unique
  19.         PT_ProductID =
  20.         CASE
  21.             WHEN PT_Value Like @Word1 THEN 'A'
  22.             WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.             WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.         END AS [Match]
  25.  FROM   [ProductText]
  26.  WHERE (PT_Value Like @Word1)
  27.     OR (PT_Value Like @Word2)
  28.     OR (PT_Value Like @Word3)
  29.  )
  30.  
  31.  SELECT   PT_ProductID
  32.  FROM     cte
  33.  GROUP BY PT_ProductID
  34.  HAVING  (COUNT([Match]) = @NumWords);
Sep 9 '10 #20
All options return

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


Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. DECLARE @NumWords int
  5.  
  6. set @word1 = '%test%';
  7. set @word2 = '%search%';
  8. set @word3 = '';
  9.  
  10. SET @NumWords = CASE ''
  11.                     WHEN @word2 THEN 1
  12.                     WHEN @word3 THEN 2
  13.                     ELSE 3
  14.                 END;
  15.  
  16. WITH cte AS
  17. (
  18. SELECT unique
  19. PT_ProductID =
  20. CASE
  21.     WHEN PT_Value Like @Word1 THEN 'A'
  22.     WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.     WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24. END AS [Match]
  25. FROM   [ProductText]
  26. WHERE (PT_Value Like @Word1)
  27. OR (PT_Value Like @Word2)
  28. OR (PT_Value Like @Word3)
  29. )
  30.  
  31. SELECT   PT_ProductID
  32. FROM     cte
  33. GROUP BY PT_ProductID
  34. HAVING  (COUNT([Match]) = @NumWords);
Sep 9 '10 #21
ck9663
2,878 Expert 2GB
Here's the corrected syntax. I did not check the logic, though...

Expand|Select|Wrap|Line Numbers
  1. /*
  2. drop table [ProductText];
  3. create table [ProductText]
  4. (
  5.    PT_Value varchar(10),
  6.    PT_ProductID varchar(5)
  7. )
  8. */
  9.  
  10. declare @word1 varchar(501)
  11. declare @word2 varchar(501)
  12. declare @word3 varchar(501)
  13. DECLARE @NumWords int
  14.  
  15. set @word1 = '%test%';
  16. set @word2 = '%search%';
  17. set @word3 = '';
  18.  
  19. SET @NumWords = CASE ''
  20. WHEN @word2 THEN 1
  21. WHEN @word3 THEN 2
  22. ELSE 3
  23. END;
  24.  
  25. WITH cte AS
  26. (
  27. SELECT 
  28. PT_ProductID, 
  29. CASE
  30. WHEN PT_Value Like @Word1 THEN 'A'
  31. WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  32. WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  33. END AS [Match]
  34. FROM [ProductText]
  35. WHERE (PT_Value Like @Word1)
  36. OR (PT_Value Like @Word2)
  37. OR (PT_Value Like @Word3)
  38. )
  39.  
  40. SELECT PT_ProductID
  41. FROM cte
  42. GROUP BY PT_ProductID
  43. HAVING (COUNT([Match]) = @NumWords); 
  44.  
  45.  
Happy Coding!!!

~~ CK
Sep 9 '10 #22
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
Sep 9 '10 #23
ck9663
2,878 Expert 2GB
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
Sep 9 '10 #24
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'.


Expand|Select|Wrap|Line Numbers
  1.  /*
  2.  drop table [ProductTextx];
  3.  create table [ProductTextx]
  4.  (
  5.     PT_Value varchar(10),
  6.     PT_ProductID varchar(5)
  7.  )
  8.  */
  9.  
  10. declare @word1 varchar(501)
  11. declare @word2 varchar(501)
  12. declare @word3 varchar(501)
  13. DECLARE @NumWords int
  14.  
  15. set @word1 = '%test%';
  16. set @word2 = '%search%';
  17. set @word3 = '';
  18.  
  19. SET @NumWords = CASE ''
  20. WHEN @word2 THEN 1
  21. WHEN @word3 THEN 2
  22. ELSE 3
  23. END;
  24.  
  25. WITH cte AS
  26. (
  27. SELECT
  28. PT_ProductID,
  29. CASE
  30. WHEN PT_Value Like @Word1 THEN 'A'
  31. WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  32. WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  33. END AS [Match]
  34. FROM [ProductText]
  35. WHERE (PT_Value Like @Word1)
  36. OR (PT_Value Like @Word2)
  37. OR (PT_Value Like @Word3)
  38. )
  39.  
  40. SELECT PT_ProductID
  41. FROM cte
  42. GROUP BY PT_ProductID
  43. HAVING (COUNT([Match]) = @NumWords);
Sep 9 '10 #25
ck9663
2,878 Expert 2GB
It looks like you're using earlier (than 2005) version of SQL Server...

Here, try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. /*
  4. drop table [ProductTextx];
  5. create table [ProductTextx]
  6. (
  7. PT_Value varchar(10),
  8. PT_ProductID varchar(5)
  9. )
  10. */
  11.  
  12. declare @word1 varchar(501)
  13. declare @word2 varchar(501)
  14. declare @word3 varchar(501)
  15. DECLARE @NumWords int
  16.  
  17. set @word1 = '%test%';
  18. set @word2 = '%search%';
  19. set @word3 = '';
  20.  
  21. SET @NumWords = CASE ''
  22. WHEN @word2 THEN 1
  23. WHEN @word3 THEN 2
  24. ELSE 3
  25. END;
  26.  
  27. SELECT PT_ProductID
  28. FROM 
  29. (
  30. SELECT
  31. PT_ProductID,
  32. CASE
  33. WHEN PT_Value Like @Word1 THEN 'A'
  34. WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  35. WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  36. END AS [Match]
  37. FROM [ProductText]
  38. WHERE (PT_Value Like @Word1)
  39. OR (PT_Value Like @Word2)
  40. OR (PT_Value Like @Word3)
  41. ) cte
  42. GROUP BY PT_ProductID
  43. HAVING (COUNT([Match]) = @NumWords); 
  44.  
Happy Coding!!!

~~ CK
Sep 10 '10 #26
NeoPa
32,081 Expert Mod 16PB
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).
Sep 10 '10 #27
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
Sep 10 '10 #28
NeoPa
32,081 Expert Mod 16PB
  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 :
Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. DECLARE @NumWords int
  5.  
  6. set @word1 = '%test%';
  7. set @word2 = '%search%';
  8. set @word3 = '';
  9.  
  10. SET @NumWords = CASE ''
  11.                     WHEN @word2 THEN 1
  12.                     WHEN @word3 THEN 2
  13.                     ELSE 3
  14.                 END;
  15.  
  16. SELECT   PT_ProductID
  17. FROM     (
  18.     SELECT DISTINCT
  19.            PT_ProductID
  20.          , CASE
  21.                WHEN PT_Value Like @Word1 THEN 'A'
  22.                WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.                WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.            END AS [Match]
  25.     FROM   [ProductText]
  26.     WHERE  (PT_Type In('0','11'))
  27.       AND ((PT_Value Like @Word1)
  28.        OR  (PT_Value Like @Word2)
  29.        OR  (PT_Value Like @Word3))
  30.     ) AS cte
  31. GROUP BY PT_ProductID
  32. 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.
Sep 10 '10 #29
This is returning an error

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


Expand|Select|Wrap|Line Numbers
  1. declare*@word1*varchar(501)
  2. declare*@word2*varchar(501)
  3. declare*@word3*varchar(501)
  4. DECLARE*@NumWords*int
  5.  *
  6. set*@word1*=*'%test%';
  7. set*@word2*=*'%%';
  8. set*@word3*=*'';
  9. *
  10. SET*@NumWords*=*CASE*''
  11.  ********************WHEN*@word2*THEN*1
  12.  ********************WHEN*@word3*THEN*2
  13.  ********************ELSE*3
  14.  ****************END;
  15.  *
  16. SELECT***PT_ProductID
  17. FROM*****(
  18.  ****SELECT*DISTINCT
  19.  ***********PT_ProductID,
  20.  ********* *CASE
  21.  ***************WHEN*PT_Value*Like*@Word1*THEN*'A'
  22.  ***************WHEN*(@NumWords*>*1)*AND*(PT_Value*Like*@Word2)*THEN*'B'
  23.  ***************WHEN*(@NumWords*>*2)*AND*(PT_Value*Like*@Word3)*THEN*'C'
  24.  ***********END*AS*[Match]
  25.  ****FROM***[ProductText]
  26.  ****WHERE**(PT_Type*In('0','11'))
  27.  ******AND*((PT_Value*Like*@Word1)
  28.  *******OR**(PT_Value*Like*@Word2)
  29.  *******OR**(PT_Value*Like*@Word3))
  30.  ****)*AS*cte
  31. GROUP*BY*PT_ProductID
  32. HAVING**(COUNT([Match])*=*@NumWords);
Sep 10 '10 #30
Dont know where all the stars **** came from, they are not in the used query
Sep 10 '10 #31
NeoPa
32,081 Expert Mod 16PB
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.
Sep 10 '10 #32
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

Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. DECLARE @NumWords int
  5.  
  6. set @word1 = '%test%';
  7. set @word2 = '%%';
  8. set @word3 = '';
  9.  
  10. SET @NumWords = CASE ''
  11.                      WHEN @word2 THEN 1
  12.                      WHEN @word3 THEN 2
  13.                      ELSE 3
  14.                  END;
  15.  
  16. SELECT   PT_ProductID
  17. FROM     (
  18.      SELECT DISTINCT
  19.             PT_ProductID,
  20.             CASE
  21.                 WHEN PT_Value Like @Word1 THEN 'A'
  22.                 WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.                 WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.             END AS [Match]
  25.      FROM   [ProductText]
  26.      WHERE  (PT_Type In('0','11'))
  27.        AND ((PT_Value Like @Word1)
  28.         OR  (PT_Value Like @Word2)
  29.         OR  (PT_Value Like @Word3))
  30.      ) AS cte
  31. GROUP BY PT_ProductID
  32. HAVING  (COUNT([Match]) = @NumWords);
Sep 10 '10 #33
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.
Sep 10 '10 #34
NeoPa
32,081 Expert Mod 16PB
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.
Sep 10 '10 #35
NeoPa
32,081 Expert Mod 16PB
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.
Sep 10 '10 #36
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
Sep 10 '10 #37
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
Sep 10 '10 #38
NeoPa
32,081 Expert Mod 16PB
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).
Sep 11 '10 #39
NeoPa
32,081 Expert Mod 16PB
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.
Sep 11 '10 #40
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

Expand|Select|Wrap|Line Numbers
  1. declare @word1 varchar(501)
  2. declare @word2 varchar(501)
  3. declare @word3 varchar(501)
  4. DECLARE @NumWords int
  5.  
  6. set @word1 = '%exterior%';
  7. set @word2 = '%securing%';
  8. set @word3 = '';
  9.  
  10. SET @NumWords = CASE ''
  11.                     WHEN @word2 THEN 1
  12.                     WHEN @word3 THEN 2
  13.                     ELSE 3
  14.                 END;
  15.  
  16. SELECT   PT_ProductID
  17. FROM     (
  18.     SELECT DISTINCT
  19.            PT_ProductID,
  20.            CASE
  21.                WHEN PT_Value Like @Word1 THEN 'A'
  22.                WHEN (@NumWords > 1) AND (PT_Value Like @Word2) THEN 'B'
  23.                WHEN (@NumWords > 2) AND (PT_Value Like @Word3) THEN 'C'
  24.            END AS [Match]
  25.     FROM   [ProductText]
  26.     WHERE  (PT_Type In('0','11'))
  27.       AND ((PT_Value Like @Word1)
  28.        OR  (PT_Value Like @Word2)
  29.        OR  (PT_Value Like @Word3))
  30.     ) AS cte
  31. GROUP BY PT_ProductID
  32. HAVING  (COUNT([Match]) = @NumWords);
Sep 13 '10 #41
ck9663
2,878 Expert 2GB
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
Sep 13 '10 #42
Example attached, the last query above sort of worked, just the couple of outstanding issues as noted in POST 41.


Thank you
Attached Files
File Type: pdf example.pdf (15.7 KB, 316 views)
Sep 13 '10 #43
ck9663
2,878 Expert 2GB
Given these 23 records, could you give me some samples of words you're searching and which records should be returned?

~~ CK
Sep 13 '10 #44
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
Sep 13 '10 #45
ck9663
2,878 Expert 2GB
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
Sep 13 '10 #46
NeoPa
32,081 Expert Mod 16PB
Input is as per lines #6 through #8 of post #41.
Sep 13 '10 #47
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"
Sep 13 '10 #48
ck9663
2,878 Expert 2GB
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
Sep 13 '10 #49
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 %%
Sep 14 '10 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by David Hitillambeau | last post: by
2 posts views Thread by A Traveler | last post: by
5 posts views Thread by Digital Fart | last post: by
20 posts views Thread by =?Utf-8?B?Q2hyaXM=?= | last post: by
6 posts views Thread by daryl | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.