Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Help with MS SQL 2005 stored procedure

Hi,

Can someone help me with some SQL? I'm trying to put together a stored procdure (MS SQL 2005).

In the database there is a table called "products" and another table called "keywords". A product can have multiple keywords. I would like to pass in mutliple keywords and have the stored procedure return a list of products which match those keywords. I would also like it to return the best match first, i,e:

Product_id          Name                Keyword_Match
---------------------------------------------------------------
1                        Product A                       5
2                        Product B                       4
3                        Product C                       2

Currently the code in the website is working in a lame way. Its using a string builder to add " OR keyword = 'key1'" multiple times to the end of the SQL statement and then passing the full statement into the database. It also doesn't return them in any particular order.

Thanks very much for any help you can give me.

Ad
0
expert-ad
Asked:
expert-ad
  • 9
  • 7
  • 5
  • +1
3 Solutions
 
jamcosCommented:
You can use where keyword in ('a', 'b', 'c') instead of building the set of ors.  
0
 
expert-adAuthor Commented:
Hi, thanks for the reply. Yes, but it still needs to be built inside the stored procedure dynamically based on how many keywords are passed in.

Also the products need to be listed in order of "best match".

Thanks anyway,
Ad
0
 
Patrick MatthewsCommented:
expert-ad said:
>>Also the products need to be listed in order of "best match".

How is best match determined?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
expert-adAuthor Commented:
I saw the best match being determined by how many of the passed in keywords relate to each product.

For example, say that the following products relate to the keywords in the keywords table like so:

Product1             -  key1, key2, key3, key4
Product2             -  key1, key5, key6, key7

If I passed in "key1, key2" then this would come back..

Product_id         Name               Keyword_Match
---------------------------------------------------------------
1                       Product 1                       2
2                       Product 2                       1

Because "key1" and "key2" are related to "product1" but only "key1" is related to "product2". Then you could just ORDER BY Keyword_Match couldn't you?

Thanks again      
0
 
jamcosCommented:
There are a few elements in play here. To get 'duplicates' returned, I'd use UNION ALL between the selects (instead of either the OR keyword = 'a' -or- keyword in('a','b') where clause). Using the where clause will eliminate the duplicates, which means you won't know how many keywords matched. Doing a UNION ALL, then GROUP BY name or ORDER BY name will help get you the counts of matching keywords.

The part I'm still hung up on is the idea of needing to pass the keywords as individual parameters into the stored proc. I would simply pass in a single variable with the list of strings comma separated or something. Perhaps I'm misinterpretting that Stored Proc requirement ?
0
 
expert-adAuthor Commented:
Hi jamcos,

Yes I'm happy to pass a string of comma seperated keywords into the stored procedure, that is actually how I thought it would best work. The problem is that the stored proecudre will need to (amongst everything else) split up the keyword string to get each keyword.

If anyone can provide me with a working stored procedure that would be great..

Ad
0
 
expert-adAuthor Commented:
I've raised the points to 400 for anyone who can provide an working stored procedure that can split up a string of comma seperated keywords and return a list of products based on those keywords ordered by the best matched product first.

Thanks again!
0
 
jamcosCommented:
As I look at it closer, I was making it harder than necessary. Here's the type of query that would give you the weighting in order of hits.
select productid, count(*) from keyword where keyword in ('b','c') group by productid order by count(*) desc
0
 
ZberteocCommented:
How are the keys stored?

1. prod1, 'key1,key2...' (all keys in one field)
2. prod1, key1, key2, ... (each key in separate column)
3 prod1,key1
   prod1,key2
   ....
 ( rows of product/key pairs)
0
 
expert-adAuthor Commented:
Hi Zberteoc,

The products and keywords are in seperate tables like so...

Products
-------------
Product_Id,
Name

Keywords
-------------
Keyword_Id,
Name

Product_Keywords
------------------------
Product_Id,
Keyword_Id

Thanks..
0
 
jamcosCommented:
CREATE PROCEDURE [dbo].[WeightedKeys]
      @p1 as nvarchar(100)
AS
BEGIN
      SET NOCOUNT ON;
   declare @SQL as nvarchar(4000)
   SET @SQL = 'select productid, count(*) from keyword where keyword in ('
   SET @SQL = @SQL + @p1 + ') group by productid order by count(*) desc'
   EXEC (@SQL)
END

To invoke this proc, you need to quote each keyword (in your string builder). Note that it takes 2 single quotes around each keyword and an extra 1 single quote at the beginning and the end.
EXEC [dbo].[WeightedKeys] @p1 = N'''b'',''c'''
0
 
jamcosCommented:
I'm just outta sync here. Each time I post, there's important info in another post at the same time:)
The cross reference table changes the proc a bit. We need to use the Keyword and Product_Keywords table joned.
   SET @SQL = 'select pk.productid, pk.count(*) from Product_Keywords pk, Keyword k where k.Name in ('
   SET @SQL = @SQL + @p1 + ') group by pk.product_id order by pk.count(*) desc'
0
 
ZberteocCommented:
Try this:

CREATE PROCEDURE spKweywordMatchSearch (@keywords_list varchar(8000))
AS
SELECT
      pk.Product_Id,
      min(pk.Name) AS Product_Name,
      CASE
            WHEN ','+@keywords_list+',' LIKE ','+kw.Name+',' THEN 1
            ELSE 0
      END                  AS KeyMatch
FROM
      Products pr
      INNER JOIN Product_Keywords pk
            ON pk.Product_Id=pr.Product_Id
      INNER JOIN Keywords kw
            ON kw.Keyword_Id=pk.Keyword_Id
GROUP BY
      pk.Product_Id
GO

EXEC spKweywordMatchSearch 'key1,key2'
0
 
ZberteocCommented:
Sorry I forgot about the order:

CREATE PROCEDURE spKweywordMatchSearch (@keywords_list varchar(8000))
AS
SELECT
      pk.Product_Id,
      min(pk.Name) AS Product_Name,
      CASE
            WHEN ','+@keywords_list+',' LIKE ','+kw.Name+',' THEN 1
            ELSE 0
      END                  AS KeyMatch
FROM
      Products pr
      INNER JOIN Product_Keywords pk
            ON pk.Product_Id=pr.Product_Id
      INNER JOIN Keywords kw
            ON kw.Keyword_Id=pk.Keyword_Id
GROUP BY
      pk.Product_Id
ORDER BY
      KeyMatch DESC
GO


ORDER BY
0
 
ZberteocCommented:
Sorry, the CASE has to be wrapped in SUM():

CREATE PROCEDURE [dbo].[spKweywordMatchSearch] (@keywords_list varchar(8000))
AS
SELECT
      pk.Product_Id,
      min(pk.Name) AS Product_Name,
      sum
      (
            CASE
                  WHEN ','+@keywords_list+',' LIKE ','+kw.Name+',' THEN 1
                  ELSE 0
            END
      )                  AS KeyMatch
FROM
      Products pr
      INNER JOIN Product_Keywords pk
            ON pk.Product_Id=pr.Product_Id
      INNER JOIN Keywords kw
            ON kw.Keyword_Id=pk.Keyword_Id
GROUP BY
      pk.Product_Id
ORDER BY
      KeyMatch DESC
GO
0
 
expert-adAuthor Commented:
Hi Zberteoc,

The last sp seemed to kind of work (might be me!). It seems to return different product id,s but the same product name:

product_id          name            KeyMatch
-------------------------------------------------
1                         Product1             1
2                         Product2             1

I assumed that this was a typo and changed the following:

"SELECT
      pk.Product_Id,
      min(pk.Name) AS Product_Name,"

to

"SELECT
      pr.Product_Id,
      min(pr.Name) AS Product_Name,"

(changed "pk" to "pr") and did so also in the group by clause..
0
 
expert-adAuthor Commented:
Sorry the last post was meant to say

product_id          name            KeyMatch
-------------------------------------------------
1                         Product1             1
2                         Product1             1
0
 
ZberteocCommented:
yes, you're right, the pr alias for product should have been there.
0
 
expert-adAuthor Commented:
Ok sorry my fault, It returns the product list as expected but it only seems to work if you pass in a single keyword,

If you do EXEC spKweywordMatchSearch 'key1' it works great,

as soon as you go EXEC spKweywordMatchSearch 'key1,key2' it doesn't
0
 
ZberteocCommented:
Sorry, my fault again! replace this line:

                  WHEN ','+@keywords_list+',' LIKE '%,'+kw.Name+',%' THEN 1

I forgot the % characters.
0
 
expert-adAuthor Commented:
Excellent, thank you!
0
 
ZberteocCommented:
You don't have to split points with "Assisted Solution" if is the same person. :), Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 9
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now