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 the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287847
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
