www.webdeveloper.com
Results 1 to 9 of 9

Thread: Challenging SQL Problems

  1. #1
    Join Date
    Dec 2004
    Location
    Eau Claire, WI
    Posts
    153

    Challenging SQL Problems

    Let me start out by explain the tables

    tbl_kb_data_Header
    =====================================
    kb_ID 'ID
    kb_Desc 'Description
    kb_Author 'Author of the Article
    kb_Status 'Active or Inactive
    kb_catID 'Category

    tbl_kb_data_Detail
    =====================================
    kb_ID 'KB ID -> Link to tbl_kb_data_Header (Many)
    kbd_Type 'Type of record (Summary, Cause, Resolution)
    kbd_Data 'KB Data for the type.

    So, the standard KB record will have one header record, and up to 7 detail records..

    Here's my problem (Using MySQL 4.1.1 by the way)..

    I need to be able to do a search on the tbl_kb_data_Detail.kbd_Data because this is where the document details come from. The problem I am having is that if the user does a search for TWO words.. Lets just say their search string is "ASP SessionVariables".

    If the the word ASP is in the first Detail record, and SessionVariables is in the second detail record, it will not find this.. Because it is looking for both words in each record.

    I need the SQL statement to think of the Entire KB document. So one header with many different detail records..

    This could pose a BIG problem for the project I am working on, so I am hoping you might have some insight..

    Thanks in advance,
    Brad
    Brad Candell - Owner/Developer
    http://www.EauClaireWEB.com - Web Development

  2. #2
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    Here's something I put together that hopefully may give you some ideas:

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-se...iplefields.asp
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  3. #3
    Join Date
    Dec 2004
    Location
    Eau Claire, WI
    Posts
    153
    Thanks for your efforts.. From what I can tell, your code explains searching multiple fields.. I am searching the same field, only possibly for multiple records. So...

    RECORD 1
    kbID = 1415
    Data = "Brad is Testing"

    RECORD 2
    kbID = 1415
    Data = "There is maybe a better way to do this"

    RECORD 3
    kbID = 1104
    Data = "Not really going to show up"


    My SQL statement works AS LONG AS the multiple words are on the same record. The challenge I have, is that I need to search all the entire contents of the kbID record. What IF I want to find all kb documents that have words "BRAD" and "BETTER". Technically, the way I want it to work, it should show kbID=1415 as a match. Because that document has both words in it. But the way the detail works, there is one word in each record.

    Does that clarify the question?
    -Brad
    Brad Candell - Owner/Developer
    http://www.EauClaireWEB.com - Web Development

  4. #4
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    If you look halfway down my article you'll see:
    <<
    o Example of searching for keywords separately for each word on multiple fields.
    >>

    This part uses Split() to set up WHERE criteria on EACH KEYWORD.

    And if you don't need the multiple fields part of course you can take that extra functionality out.
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  5. #5
    Join Date
    Dec 2004
    Location
    Eau Claire, WI
    Posts
    153
    Pardon my ignorance, but I don't see how this is going to solve my problem. I need it to search the same field on multipe records, not multiple fields. It appears to me atleast, that in that example it shows how to search multiple fields in the same record.

    Maybe I am just misunderstanding the example. Did my example make sense?
    Brad Candell - Owner/Developer
    http://www.EauClaireWEB.com - Web Development

  6. #6
    Join Date
    Dec 2004
    Location
    Eau Claire, WI
    Posts
    153
    By the way, I appreciate your all of your help and efforts..
    Brad Candell - Owner/Developer
    http://www.EauClaireWEB.com - Web Development

  7. #7
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    Pardon my ignorance, but I don't see how this is going to solve my problem. I need it to search the same field on multipe records, not multiple fields. It appears to me atleast, that in that example it shows how to search multiple fields in the same record.
    If someone types in "Lincoln book" it would find records with Lincoln and with book instead of only records that have the exact phrase "Lincoln book"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  8. #8
    Join Date
    Dec 2003
    Location
    England, UK
    Posts
    432
    i see your problem there. you'd need all the detail records in the same tuple in the results set to be able to use bullschmidt's solution, which is not feasible because of the varied number of records.

    you could do with sticking them all together temporarily to search them. not sure how you'd do this with mysql but i know its possible with sql server using a text field.

  9. #9
    Join Date
    Jun 2007
    Posts
    1
    Hi this is Pardhu

    You can solve this issue using match("columnName") against('words')

    select * from tbl_kb_data_Detail where match(kbd_Data) against('ASP SessionVariables')

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles