Well.. my queries use to work before but now it does not.
I have a collection question for everyone.
How do you or what approach would you use if I have to a search on a collection on up
to 10 tables in one query and then get a unique key to grab the record set after the
search?
I have one and the results are correct but as you know <cfindex> requires a unique key
to extract the record(s).
any suggestions?
for example my query to populate the collection is:
SELECT tbl_KnowledgebaseIDLog.KBKeyID,
tbl_KnowledgebaseIDLog.isActive,
tbl_KnowledgebaseIDLog.isDelete,
tbl_KnowledgebaseIDLog.kbid,
tbl_Knowledgebase.kbID,
tbl_Knowledgebase.kbTitle,
tbl_Knowledgebase.kbDocType,
tbl_Knowledgebase.Abstract,
tbl_Knowledgebase.notes,
tbl_Knowledgebase.links,
tbl_Knowledgebase.isFileAttached,
tbl_Knowledgebase.hasImage,
tbl_Knowledgebase.isActive,
tbl_Knowledgebase.isDelete,
tbl_DocumentType.documentTypeID,
tbl_DocumentType.documentType,
tbl_DocumentType.isActive,
tbl_DocumentType.isDelete,
tbl_SubjectTerm.subjectid,
tbl_SubjectTerm.subject,
tbl_SubjectTerm.isActive,
tbl_SubjectTerm.isDelete,
tbl_KnowledgebaseSubjectTermAssociation.kbid,
tbl_KnowledgebaseSubjectTermAssociation.subjectid,
tbl_KnowledgebaseSubjectTermAssociation.isActive,
tbl_KnowledgebaseSubjectTermAssociation.isDelete,
tbl_Category.categoryid,
tbl_Category.category,
tbl_Category.isActive,
tbl_Category.isDelete,
tbl_Subcategory.subcategoryid,
tbl_Subcategory.subcategory,
tbl_Subcategory.isActive,
tbl_Subcategory.isDelete,
tbl_CategorySubcategoryAssociation.categoryID,
tbl_CategorySubcategoryAssociation.subcategoryID,
tbl_CategorySubcategoryAssociation.isActive,
tbl_CategorySubcategoryAssociation.isDelete,
tbl_KnowledgebaseCategoryAssociation.kbid,
tbl_KnowledgebaseCategoryAssociation.subcategoryid,
tbl_KnowledgebaseCategoryAssociation.isActive,
tbl_KnowledgebaseCategoryAssociation.isDelete,
tbl_Event.eventid,
tbl_Event.eventName,
tbl_Event.isActive,
tbl_Event.isDelete,
tbl_KnowledgebaseEventAssociation.kbid,
tbl_KnowledgebaseEventAssociation.eventid,
tbl_KnowledgebaseEventAssociation.isActive,
tbl_KnowledgebaseEventAssociation.isDelete,
tbl_Casestudy.casestudyid,
tbl_Casestudy.project,
tbl_Casestudy.isActive,
tbl_Casestudy.isDelete,
tbl_KnowledgebaseCasestudyAssociation.kbid,
tbl_KnowledgebaseCasestudyAssociation.casestudyid,
tbl_KnowledgebaseCasestudyAssociation.isActive,
tbl_KnowledgebaseCasestudyAssociation.isDelete
FROM tbl_KnowledgebaseIDLog,
tbl_Knowledgebase,
tbl_DocumentType,
tbl_SubjectTerm,
tbl_KnowledgebaseSubjectTermAssociation,
tbl_Category,
tbl_SubCategory,
tbl_CategorySubcategoryAssociation,
tbl_KnowledgebaseCategoryAssociation,
tbl_Event,
tbl_KnowledgebaseEventAssociation,
tbl_Casestudy,
tbl_KnowledgebaseCasestudyAssociation
WHERE tbl_KnowledgebaseIDLog.kbid = tbl_Knowledgebase.kbID
and
tbl_Knowledgebase.kbDocType = tbl_DocumentType.documentTypeID
AND
tbl_KnowledgebaseSubjectTermAssociation.kbid = tbl_Knowledgebase.kbID
and
tbl_KnowledgebaseSubjectTermAssociation.kbid = tbl_KnowledgebaseIDLog.kbid
and
tbl_KnowledgebaseSubjectTermAssociation.subjectid = tbl_SubjectTerm.subjectid
and
tbl_CategorySubcategoryAssociation.categoryID = tbl_Category.categoryid
and
tbl_CategorySubcategoryAssociation.subcategoryID = tbl_Subcategory.subcategoryid
and
tbl_KnowledgebaseCategoryAssociation.kbid = tbl_Knowledgebase.kbID
and
tbl_KnowledgebaseCategoryAssociation.kbid = tbl_KnowledgebaseIDLog.kbid
and
tbl_KnowledgebaseCategoryAssociation.subcategoryid =
tbl_CategorySubcategoryAssociation.subcategoryID
and
tbl_KnowledgebaseEventAssociation.kbid = tbl_Knowledgebase.kbID
and
tbl_KnowledgebaseEventAssociation.kbid = tbl_KnowledgebaseIDLog.kbid
and
tbl_KnowledgebaseEventAssociation.eventid =tbl_Event.eventid
and
tbl_KnowledgebaseCasestudyAssociation.kbid = tbl_Knowledgebase.kbID
AND
tbl_KnowledgebaseCasestudyAssociation.kbid = tbl_KnowledgebaseIDLog.kbid
and
tbl_KnowledgebaseCasestudyAssociation.casestudyid = tbl_Casestudy.casestudyid
and
tbl_DocumentType.isActive = '1'
AND
tbl_DocumentType.isDelete = '0'
AND
tbl_Casestudy.isActive = '1'
and
tbl_Casestudy.isDelete = '0'
and
tbl_Event.isActive = '1'
and
tbl_Event.isDelete = '0'
and
tbl_KnowledgebaseCasestudyAssociation.isActive = '1'
and
tbl_KnowledgebaseCasestudyAssociation.isDelete = '0'
and
tbl_KnowledgebaseEventAssociation.isActive = '1'
and
tbl_KnowledgebaseEventAssociation.isDelete = '0'
and
tbl_Category.isActive = '1'
and
tbl_Category.isDelete = '0'
and
tbl_Subcategory.isActive = '1'
and
tbl_Subcategory.isDelete = '0'
and
tbl_CategorySubcategoryAssociation.isActive = '1'
and
tbl_CategorySubcategoryAssociation.isDelete = '0'
and
tbl_KnowledgebaseCategoryAssociation.isActive = '1'
and
tbl_KnowledgebaseCategoryAssociation.isDelete = '0'
and
tbl_SubjectTerm.isActive = '1'
and
tbl_SubjectTerm.isDelete = '0'
and
tbl_KnowledgebaseSubjectTermAssociation.isActive = '1'
and
tbl_KnowledgebaseSubjectTermAssociation.isDelete = '0'
and
tbl_KnowledgebaseIDLog.isActive = '1'
and
tbl_KnowledgebaseIDLog.isDelete = '0'
and
tbl_Knowledgebase.isDelete = '0'
ORDER BY tbl_Knowledgebase.kbTitle ASC,
tbl_SubjectTerm.subject ASC,
tbl_Subcategory.subcategory ASC
Long enough? ;-)
thanks,
chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
