If your RDBMS doesn't support the creation of some sort of database link (which lets you query another database from within the RDBMS), you'll probably have to loop over the results and build up a query result set manually.
On 9/5/07, Joy Holman <[EMAIL PROTECTED]> wrote: > > 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. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287849 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
