Posted on 2012-08-27
Last Modified: 2012-08-31

We have a SQL database of 'parts' and 'models' which those parts fit.  The only link from each model to all of its parts is in text fields.  We use the following query to create a string which identifies all the models each part fits.

The problem is that it only works for one part at a time as defined in the WHERE clause.

We would like to:

1. Expand the query so that it would update the Part.Description text field and append the string,


2. Expand the query so that it would update the Part.Description text field for multiple parts at the same time.

Thank You!

SELECT @HRefList = COALESCE(@HRefList,' ') + HREF
CAST(Categories.CategoryID AS varchar(5)) +
CAST(Categories.CategoryName AS varchar(100)) AS HREF
FROM Categories
WHERE Categories.Custom_Where_Clause LIKE '%2031083%') AS HRefTable
SELECT @HRefList AS FitsModels

(The complete query is a little longer as it concatenates the text necessary to produce a list of hyperlinks which appear in the part description so that a customer can click through to any model which a part fits directly from the description of the part).
Question by:VPWcom
    LVL 8

    Accepted Solution

    For your sake I hope all you part numbers are 6 digits long....
    Because LIKE '%2031083%'  will also match 12031083, 92031083, etc...
    And is there a delimiter between the numbers?

    I'm not sure I understand exactly what you are trying to accomplish, but if you control the Database, then your best option would probably be to first create a new link table, that will be 2 simple fields: the primary key for the model, and the primary key for the part.

    you don't even need to do this from the software, you can build a ON INSERT trigger on the model table that would populate automatically  that link table.

    From there it would be a lot easier (and a lot more efficient) to create a list of the parts for each model.
    And it would also allow you to do a function that would return the list of all models for a part, when you need to display that.
    LVL 13

    Assisted Solution

    by:Jesus Rodriguez
    Create a cursor for the parts and then update the description according models the description. Will be a long process because the structure of your tables
    More or less THE IDEA WILL BE something like this (You have to create your own querys)

        FROM MODEL

    LVL 8

    Expert Comment

    PS: Concatening string may look like a good idea, but if your description field allows 255 caracters and you concatenate 20 model, I hope your model are very short, because you risk going over the allowed length of your field...

    It would be better to look at what your real needs are, then look at the differents ways to get what you need.
    And only then implement the solution you choose.
    I really doubt you need to put model in parts description field, you'll just create more problems when you'll need to do new stuff.
    I really think the way to get all your current needs, and future needs, is to have a separate table that link models to parts, and from there it will be easy do what you need to do

    Assuming it is to do things like list with hyperlinks the models that can use a specific part, or the opposite list links to parts for a model.
    LVL 13

    Expert Comment

    by:Jesus Rodriguez
    I'm agree with stalhw. I think that is time that you to purify your database becasue you can run into a bigger problem in hte future. I give you an option but your final point will be doing this because you can mess up your whole database and updating sometimes description that doesn't match with the models.

    Author Closing Comment

    Thank you for the feedback.  I should have added to the question that we do not control the database, so we are - by definition - seeking a poor solution from a design point of view.  That said, I think the CURSOR idea would probably be a means to achieve the outcome, but I am already well beyond the limit of my SQL training or experience, so am nervous about venturing too far from either SELECT or simple UPDATE scenarios.  Since we only have query access through a web interface I am not sure if adding or dropping tables or cursors or stored procedures are allowed or possible.  Since this was a one time effort, I was able to extract all the CategoryID's (models) and Custom_Where_Clause (list of parts) fields, reduce the C_W_C to a comma separated list and pivot the data in Excel to produce a list of models for each part!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now