Challenging SQL Problems

[candelbc]

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

[Bullschmidt]

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

[candelbc]

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

[Bullschmidt]

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.

[candelbc]

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?

[candelbc]

By the way, I appreciate your all of your help and efforts..

[Bullschmidt]

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"

[russell_g_1]

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.

[pardhumeduri]

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')