ISSUE: I'm working with existing functionality, so I need to work this code in somehow or rewrite a significant amount of code.
I am using tables that come from different datasources. I can't use the outer join with QofQ, right? I need to use an outer join to combine two result sets that don't have same number of matches. GOAL: I want to end up with final results so that any record from qdocSourceSort with a fac_ID value has its sourceTitle field populated with the sourceTitle created in qqFacTitle. (qdocSourceSort already has the fac_id NULL value records populated with SourceTitle values.) My main objective is to sort all these records by SourceTitle! CODE: The two tables I need to combine are qdocSourceSort and qqFacTitle below. (qqSourceFacNo (below) is an intermediate step.) I want all these records: <cfquery name="qdocSourceSort" datasource="#request.peds#"> select * from PEdocs, Sources WHERE deleted IS NULL and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#" cfsqltype="CF_SQL_CHAR" maxlength="1"> and fk_sourceID = sourceID </cfquery> I want to combine the results above with the last query of the two below. <cfquery dbtype="query" name="qqSourceFacNo"> select sourceFacID from qdocSourceSort where sourceFacID IS NOT NULL </cfquery> <cfquery name="qqFacTitle" datasource="#request.generalds#"> select fac_ID, fac_Facility AS sourceTitle from IHSFacility where fac_ID IN (#ValueList(qqSourceFacNo.sourceFacID)#) </cfquery> qqFacTitle.fac_ID would match some of the qdocSourceSort.fac_ID values. The rest of the qdocSourceSort.fac_ID values would be NULL. Also numerous qdocSourceSort.fac_ID values could be the same number, so each result from qqFacTitle.fac_ID needs to be matched as many times as it is referenced in qdocSourceSort.fac_ID values. qdocSourceSort has a column SourceTitle, so each result of qqFacTitle.SourceTitle needs to be merged into the SourceTitle fields in a qdocSourceSort record wherever qqFacTitle.fac_ID matches qdocSourceSort.fac_ID. Can anybody help me?!?!? Thanks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287855 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4