Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006

    Unanswered: problem with insert id from a search result

    Hi everybody have two tables listed below I need to update the articolodonnaid on tabletags based on a wildcard compare of tbltags.descrizionericerca against tblarticolodonna.titoloarticolodonna
    it updates artocolodonnaid if it's value is null if not it inserts it as a new record.... and it loops over to do each record.. have this query below but couldn't continue to do the rest..

    articoloDonnaID (PK int not null)
    titoloArticoloDonna varchar(100)

    ricercaID (PK int not null)
    descrizionericerca varchar(50)
    articolodonnaid int

    DECLARE @ricercaidmax int
    DECLARE @ricercaidmin int
    set @ricercaidmin=2514
    set @ricercaidmax=5129

    WHILE @ricercaidmin <= @ricercaidmax


    select a.ArticoloDonnaID,a.TitoloArticoloDonna
    from tblArticoloDonna a
    where (select t.descrizionericerca from tbltags t
    where a.titoloarticolodonna like '%'+ t.descrizionericerca +'%')

    and ricercaid=@ricrcaidmin

    SET @ricercaidmin = @ricercaidmin + 1


    thanks for any help

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    I think you need a cross-table between your articles table and tags table

      tagId int not null,
      tagDescription varchar(100) not null,
      constraint pk_tag primary key (tagId)
    CREATE TABLE article(
      articleId int not null,
      articleDescription varchar(250) not null,
      constraint pk_article primary key (articleId )
    CREATE TABLE article_tags(
      articleId int not null,
      tagId int not null,
      constraint pk_article_tags primary key (articleId, tagId)
    To fill your cross table:
    INSERT INTO article_tags (articleId, tagId)
    SELECT articleId, tagId
    FROM article
         INNER JOIN  tag ON
            articleDescription LIKE '%' + tagDescription + '%'
    But I would not to it that way. Let someone select the tags that describe an article, and store those in the article_tags table.

    If you want to continue to work like this, you don't even need this junction table. You can do the selection at run-time to decide if an article is described by a tag. Just add the INNER JOIN part to your query. But it would severely limit the flexibility of your system. To the article "USB stick XYZ120 20 GB" you could at most assign the significant tags "USB" and "stick".

    There would be no way to also assign the tags "PC-peripherals", "external memory", "flash" or you would have to include all those strings in the articleDescription column (yuk).

    Separate the name of your articles from the tags you want to assign to them. Then there is no limit on your possibilities, like adding "promotion" during a certain period to a number of articles. Searching for "promotion" would yield all articles in promotion at that time.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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