Posted by StevePBurgess on 12/28/06 14:32

I have a database which stores documents (e.g. policies and guidelines)
in our content management system. The follow relationships exist:

Documents (1-to-Many) LinkTableKeywordLink (Many-to-1) Keywords

Documents (1-to-Many) LinkTableAttachments (Many-to-1) Attachments

Documents (1-to-Many) LookUpSubjects

Documents (1-to-Many) LookUpDocType

When the user conducts a search I want the SQL to check if the string
they enter is present in:

* the Title, Author, Summary fields of Documents OR
* the Title or Path of the Attachments
* the Keywords that are links OR
* the Subject that is linked OR
* the DocType that is linked.

Is this possible in one SQL query. I have tried the following:

SELECT DISTINCT Documents.Document, Documents.Title,Priority

FROM Documents,
Attachments,Keywords,LinkTableAttachments,LinkTableKeywordLink

WHERE
((Documents.Document=LinkTableAttachments.DocumentID and
LinkTableAttachments.AttachmentID=Attachments.Id) OR
(Documents.Document=LinkTableKeywordLink.DocumentID and
LinkTableKeywordLink.KeywordID=Keywords.Id))

AND (Documents.Title Like '%SEARCHSTRING%' OR Documents.Author Like
'%SEARCHSTRING%' OR Documents.Summary Like '%SEARCHSTRING%' OR
Keywords.Keyword Like '%SEARCHSTRING%' OR DocType.DocType Like
'%SEARCHSTRING%' OR Attachments.AttachmentTitle Like '%SEARCHSTRING%'
OR Attachments.Path like '%SEARCHSTRING%' or Subjects.Subject Like
'%SEARCHSTRING%')

AND StartDate<=getDate() and ReviewDate>getDate() order by Title


but this causes, perhaps understandably, a timeout error.

Any thoughts?

Thanks!
Steve

[Back to original message]