Manipulating a result set ?

[Gill1978]

Hi !

I've got a table of data in a cf <table>, the data is generated by one query, except for the last column of data, which is generated by loopin through the original query to get the right dates from another table.

I need to know sort on that last column but i don't know how? I can't join the two tables to get all the data because the null values in one of the tables don't get pulled out by the sql query.

Can anyone help?

Thanks

Julie

 

[pcorreia]

It sort of sounds like you could use an outer join to accomplish what you're describing. Have you considered this? Perhaps you'd post a code sample so we can help you.

Short of that, if you're using CF5 it's possible to add the dates from the second query to the result set from the first query -- then you could do a query of queries to sort the result set by the new column.

Let's see some code! ;o)

 

[Gill1978]

This is the first query:

<cfquery DATASOURCE=&quot;#application.dsn#&quot; NAME=&quot;GetProjects&quot;>
SELECT DISTINCT p.ProjId AS statProjId, p.ProjName, p.projonhold, p.ProjInvitedDate, p.UserId as p_userid,
ps.ProjStatusLev2 AS p_status, p.projectStatusId, p.projid, p.projtype, p.sectorid, p.projentereddate, p.projresponsereq, u.UserId, u.UserSurname, u.UserForenames, cr.relaId AS user_relation

FROM TBL_project p, TBL_Project_Status ps, TBL_User u,
TBL_ConsultantsRelationshipToProject cr

WHERE u.userid = #session.userid#
AND ps.ProjectStatusId = p.ProjectStatusId
AND cr.UserId = u.userid
AND cr.projid = p.projid
AND cr.relaId IN (#relationships#)
AND (p.ProjectStatusId != 1 and cr.relaId !=1 or cr.relaId !=3)
AND cr.Inactive <> 1
AND p.ProjInactive <> 1
</cfquery>

I've tried using an outer join but i don't get back ALL the results i need.

The p.ProjId is looped through the second query to and put in the next column of the coldfusion table, but how do I do a query of queries to sort on the new column?

The second query is :

<cfoutput query=&quot;GetProjects&quot;>

<cfquery DATASOURCE=&quot;#application.dsn#&quot; NAME= &quot;InviteDate&quot;>

SELECT distinct (d.ProjectId), DistDateInviteSent
FROM tbl_distributionInvitedList d
WHERE Projectid = #GetProjects.ProjId#
AND (userid = #session.userid#)
AND d.Inactive <> 1
</cfquery>

<cfif user_relation is 2 OR user_relation is 3>

#DateFormat(InviteDate.DistDateInviteSent, &quot;dd/mm/yyyy&quot#

</cfif>

</cfoutput>

Thanks
Jules
p.s. I left out all code to do with the other columns ....

 

[pcorreia]

I hate to beat the outer join thing to death, but shouldn't this accomplish what you're looking to do?

<cflock timeout=&quot;20&quot; type=&quot;READONLY&quot; scope=&quot;APPLICATION&quot;>
   <cfset REQUEST.dsn = APPLICATION.dsn>
</cflock>
<cfquery name=&quot;GetProjects&quot; datasource=&quot;#REQUEST.dsn#&quot;>
SELECT DISTINCT
   p.ProjId AS statProjId, 
   p.ProjName, 
   p.projonhold,  
   p.ProjInvitedDate,  
   p.UserId as p_userid,
   p.projectStatusId, 
   p.projid, 
   p.projtype, 
   p.sectorid, 
   p.projentereddate, 
   p.projresponsereq, 
   ps.ProjStatusLev2 AS p_status, 
   u.UserId, 
   u.UserSurname, 
   u.UserForenames, 
   cr.relaId AS user_relation
FROM
   TBL_project AS p
   INNER JOIN TBL_Project_Status AS ps ON ps.ProjectStatusId = p.ProjectStatusId
   INNER JOIN TBL_User AS u ON cr.UserId = u.userid 
   INNER JOIN TBL_ConsultantsRelationshipToProject AS cr ON cr.projid = p.projid
   LEFT JOIN tbl_distributionInvitedList AS d ON p.ProjId = d.ProjId
WHERE 
   u.userid = #session.userid# 
   AND cr.relaId IN (#relationships#)
   AND (p.ProjectStatusId != 1 and cr.relaId !=1 or cr.relaId !=3)
   AND cr.Inactive <> 1
   AND p.ProjInactive <> 1 
   AND d.Inactive <> 1
</cfquery>

Also, are you aware you are selecting the same value (projID) twice with different names?

 

[pcorreia]

Forgot to add d.DistDateInviteSent to the SELECT clause. Sorry.

 

[pcorreia]

Questions about query of queries are answered in duplicate thread thread232-288225