Hi Joy, There may be a work around.
If they are both MS SQL Servers for example... If the two datasources point to the same server and just different databases, you can do it inline with your SQL Statement assuming they share the same user credentials. However, I'll assume this is not the case. If the two data sources are pointing to two different MSSQL Servers, you can still join the two. MS SQL Server allows you to add a "Linked Server". Just add this through Enterprise Manager which acts as a pointer to the other database server. Once you do this, with the correct login/authentication credentials, You can then access and join two databases on two separate MSSQL Servers using SQL. You can even just create a view on the one that points to the table on the other and then join the table/s and the newly created view locally. This would then allow you to do the join using just one or the other datasource. Does that make sense? If that's not an option and you are still running windows, you can even create a simple MS Access DB and then add the two databases using THAT "linked table" option which will get you the same result. An aside: I have even created a linked server from MSSQL Server to an Oracle database running on UNIX and was able to join the two in the past although I wouldn't do this in production due to possible performance issues. My guess is that if you aren't using windows, there may be similar solutions for other databases. Oracle uses something call heterogeneous connections or something which didn't work well in the past but that was 4 years ago I think. Best of luck! Vince Collins http://www.vincentcollins.com Joy Holman 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. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:287913 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
