Figured it out. I was missing a join. Should have been:
SELECT DISTINCT phases.phase AS phaseLabel,
narratives.title,
narratives.id,
narratives.content,
phases.id AS phaseId
FROM ( ( nsftool.phases2company phases2company
INNER JOIN
nsftool.phases phases
ON (phases2company.phaseId = phases.id))
INNER JOIN
nsftool.narratives narratives
ON (narratives.phase = phases.id))
INNER JOIN
nsftool.narratives2case narratives2case
ON (narratives2case.narrativeId = narratives.id)
AND (phases2company.caseId = narratives2case.caseId)
WHERE (narratives2case.caseId = <cfqueryparam cfsqltype="cf_sql_numeric"
null="no" value="#trim(caseId)#" />)
ORDER BY phases2company.displayRank ASC, narratives.displayRank ASC
On Fri, Oct 15, 2010 at 1:35 PM, Michael J. Sprague
<[email protected]>wrote:
>
> I am having a SQL problem that I'm guessing amounts to me just being brain
> dead today but I'd really appreciate any help with this. Here is the query:
>
> SELECT phases.phase AS phaseLabel,
> narratives.title,
> narratives.id,
> narratives.content,
> phases.id AS phaseId
> FROM (( nsftool.narratives2case narratives2case
> INNER JOIN
> nsftool.narratives narratives
> ON (narratives2case.narrativeId = narratives.id))
> INNER JOIN
> nsftool.phases2case phases2case
> ON (phases2case.caseId = narratives2case.caseId))
> INNER JOIN
> nsftool.phases phases
> ON (phases2case.phaseId = phases.id)
> WHERE (narratives2case.caseId = <cfqueryparam cfsqltype="cf_sql_numeric"
> null="no" value="#trim(caseId)#" />)
> ORDER BY phases2case.displayRank ASC, narratives.displayRank ASC
>
> It is almost working but it is currently returning the same narrative
> titles
> and content for each phaseId. What I need to do is return the phases for a
> particular case ordered by their phase display rank and return the
> associated narrative content ordered by the narrative display rank.
> Currently it returns the same set of narrative content for each phase. Any
> ideas?
>
> Thanks in advance for any help with this.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338239
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
