Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: weighted search on fulltext index

    SQL Server 2005:
    Anyone know how best to rewrite this SQL string to perform a weighted search on my table?
    here's my code:
    Code:
    SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')"
    the idea is to get a list of recipes most pertinant to the ingredients entered.
    table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.

    at the moment it returns the correct recipes but not in the right order.
    If I use 'CONTAINS' it returns nothing.
    hope that's not too vague!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Umm...

    Can you provide us with more information on your table structure please?
    Something sounds fishy...
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by darkmunk
    the idea is to get a list of recipes most pertinant to the ingredients entered.
    the place to start should be to normalize your table

    unless i misunderstand, you have ing01, ing02, ing03, ...

    split these off into a one-to-many recipe_ingredients table

    why is it a good idea to achieve 1NF? because it will make your queries simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    yes, I thought I might be told to normailise it.
    this is a test structure based on the access db given to me. here's the ddl for the two tables. - when the user clicks on a recipe name in the results list they will be taken to the recipe cooking method page which gets it's data from the recipeMethod table, at the moment they are not linked but that's another thing.
    recipe ingredients table
    Code:
    [RecipeID] int IDENTITY(1, 1) NOT NULL,
      [RecipeName] varchar(255) COLLATE Latin1_General_CI_AS,
      [Type] int,
      [Ingred01] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred02] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred03] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred04] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred05] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred06] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred07] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred08] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred09] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred10] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred11] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred12] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred13] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred14] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred15] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred16] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred17] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred18] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred19] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred20] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred21] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred22] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred23] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred24] varchar(max) COLLATE Latin1_General_CI_AS,
      [Ingred25] varchar(max) COLLATE Latin1_General_CI_AS,
      [timeStamp_RL] timestamp NOT NULL,
    and the method table:
    Code:
    [RecipeID] int IDENTITY(1, 1) NOT NULL,
      [RecipeName] varchar(max) COLLATE Latin1_General_CI_AS,
      [Type] int,
      [Method01] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method02] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method03] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method04] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method05] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method06] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method07] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method08] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method09] varchar(max) COLLATE Latin1_General_CI_AS,
      [Method10] varchar(max) COLLATE Latin1_General_CI_AS,
      [timeStamp_RM] timestamp NOT NULL,

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yikes-a-roni

    1NF should be your next goal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow...
    Oh my days!

    That is a horrible, horrible design!
    I'm afraid I'm not going to help you to put a sticking plaster on this - you need to go and re-design - I am happy to help you with that!

    Pounding A Nail: Old Shoe or Glass Bottle?
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    te he...
    I am more than happy to recieve any advice you can give me concerning the best way to build these tables and then query them. The more of this I find myself having to do the more I realise I don't know.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    split these off into a one-to-many recipe_ingredients table
    That'd be where I'd start (same goes for the methods table).
    How is your knowledge and undesrtanding of normalization? I suggest you take a read of this article and have a go. Feel free to report back for comments and advice on your attempts
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    my knowledge of normalisation is approx. nill, I know that everyont tells me to do it but but my poor overworked brain just won't accept the technique! I can't get my head round how it makes my modest little databases better. Although the other day it would have helped my other badly designed DB from filling up. anyway, hey ho.
    Anyone want some freelance work designing this for me?
    I am sure that for you guys this is a simple job. please PM me for more info.

  10. #10
    Join Date
    Jul 2007
    Posts
    96
    Check this link http://databases.about.com/od/specif...malization.htm . It might not be the best reading about normalization but it will give you some lights on the subject.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your design is a "recipe" for disaster!
    Sorry...couldn't resist.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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