Professional Web Applications Themes

Thanks Works now. - Microsoft SQL / MS SQL Server

Thanks for the help guys. CELKO, sorry if all that was elementary. I'm new to this. Like I mentioned this was imported from Access so I didn't bother changing the table and field names from the Leysinski scheme. Sorry, I'm new to SQL Server (and to db design for that matter), but I will read ISO-11179 now that it's been brought to my attention. Thanks. CELKO, to answer your question, each keyword is a record in the "tblKeywords" table and the keyword is referenced by a related product id in "tblProducts". tblKeywords doesn't have a Primary Key and (as I'm ...

  1. #1

    Default Thanks Works now.

    Thanks for the help guys. CELKO, sorry if all that was elementary. I'm new
    to this. Like I mentioned this was imported from Access so I didn't bother
    changing the table and field names from the Leysinski scheme. Sorry, I'm new
    to SQL Server (and to db design for that matter), but I will read ISO-11179
    now that it's been brought to my attention. Thanks.

    CELKO, to answer your question, each keyword is a record in the
    "tblKeywords" table and the keyword is referenced by a related product id in
    "tblProducts". tblKeywords doesn't have a Primary Key and (as I'm sure
    you've guessed) the relationship from tblProducts to tblKeywords is
    one-to-many.

    This touches on my next question: The result set will have duplicates in it
    (one for each keyword)
    Is there a way to use T-SQL to remove the duplicates? If not I could easily
    do this from VB once the data is returned but I am curious if there is a way
    to save a step.

    I didn't design the db, I'm just being asked to aid in a migration from
    Access to SQL and this is a "test the waters" db.
    If I omitted anything I apologize.

    Thanks Again;

    Grant
    >>>>>
    ---------------------------------------------------------------------------

    First, read ISO-11179 about how to name data elements, so you'll stop
    using those confusing, silly, redudant prefixes that make your SQL look
    like 1960's BASIC.

    You did not post DDL or specs, so do you have one keyword per row in the
    Keywords table, or a list of comma separatd values?

    A CHAR(n) is always (n) characters long thanks to blank padding, so your
    parameter probably had a lot of blanks in it. If you are worried about
    any blanks in the string, use REPLACE() instead of trim.

    Try this version:

    CREATE PROCEDURE SearchProducts_Basic
    (searchcrit VARCHAR(75)) -- data type change!!
    AS
    SELECT P1.lng_product_id, P1.sku, P1.name,
    P1.category, P1.image, K1.keywords,
    C1.category
    FROM Categories AS C1,
    Products AS P1,
    Keywords AS K1
    WHERE C1.category = P1.category
    AND P1.lng_product_id = K1.lng_product_id
    AND K1.keyword
    LIKE '%' + LTRIM(RTRIM(searchcrit)) + '%'
    ORDER BY P1.sku;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!

    >>>>>>>
    -- -------------------------------------------------------------------------
    --
    CREATE PROCEDURE dbo.ADT_sp_ProductSearch_Basic

    -- Parameter Declaration

    (
    searchCrit char(75)
    )

    -- Begin

    AS

    SELECT
    tblProducts.lngProductID,
    tblProducts.strSKUnumber,
    tblProducts.strname,
    tblProducts.strcategory,
    tblProducts.strImage,
    tblKeywords.strKeywords,
    tblCategory.strCategory
    FROM
    (tblCategory INNER JOIN tblProducts ON tblCategory.strCategory =
    tblProducts.strcategory)
    INNER JOIN tblKeywords ON tblProducts.lngProductID =
    tblKeywords.lngProductID
    WHERE
    ( tblKeywords.strKeywords LIKE '%' + searchCrit + '%' )
    ORDER BY tblProducts.strSKUnumber;
    -- -------------------------------------------------------------------------


    Grant Harmeyer Guest

  2. #2

    Default Re: Thanks Works now.

    > Is there a way to use T-SQL to remove the duplicates?

    SELECT DISTINCT


    Aaron Bertrand - MVP Guest

Similar Threads

  1. #39370 [NEW]: $_GET no longer works under FastCGI but works under CGI.
    By trustpunk at gmail dot com in forum PHP Bugs
    Replies: 7
    Last Post: November 9th, 10:38 AM
  2. how it works
    By Ravi in forum ASP.NET Building Controls
    Replies: 2
    Last Post: December 1st, 10:53 PM
  3. 6$ into $10 000 THIS REALLY WORKS!!!!
    By HereToServeYou in forum PHP Development
    Replies: 0
    Last Post: September 18th, 06:50 AM
  4. IT REALLY WORKS!!!!
    By Ritchy Ritch in forum Informix
    Replies: 0
    Last Post: November 2nd, 11:07 AM
  5. Works on mac --- not on pc ---WHY???
    By Dan Vendel *GOF* in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 11th, 03:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139