Hey All,

I have a SQL JOIN / Output question.
So for this simple CMS I have built I two tables I am joinging.
An "issues" table (as in magazine issue) and a pages table.
Each one has PK's, issues is iid, pageis pgid. the pages table has an iid
field in it as well for relationship purposes.

So what I want to do is output a list of all issues, with associated pages
underneath each issue listing.
I thought a simple outer join would handle this, then use <CFOUTPUT> with a
group by parameter on iid to output the issus and a regular output for the
pages.

The problem I am having is that if there are no pages associated with an issue
the issue heading doesnt output. Not sure if my problem is in my query or not
so I thought I would post here. You will see in my output code as well that I
tried a CFIF on a column alias to see if the pages were null to output a
message, but it seems to work erratically so ignore if you like...

Database: Access 2000
CFVersion: MX7

My Query:
<cfquery datasource="#session.dsn#" name="get_news2">
SELECT pages.*, issues.*, issues.iid as tstiid FROM issues LEFT JOIN pages ON
issues.iid = pages.iid WHERE issues.cid = #session.cid# ORDER BY
issues.iid,issues.tmst DESC
</cfquery>


<cfoutput query="get_news2" group="iid">
<tr bgcolor="666666" class="header-sm">
<td align="Left" width="110">Publication Name:</td>
<td align="left">#pub_name#</td>
<td align="right"><a
href="index.cfm?fuseaction=createpage.choose&iid=# tstiid#&cid=#session.cid#"><sp
an class="header-sm">Create a New Page</span></a></td>
</tr>
<tr bgcolor="cccccc" class="text">
<td align="left" width="110"><strong>Page Name</strong></td>
<td align="Left"><strong>Security</strong></td>
<td align="right"><strong>Actions</strong></td>
</tr>
<cfoutput>
<CFSET Class = IIF(get_news2.CurrentRow MOD 2 EQ 0, "'WhiteSmoke'",
"'White'")>
<cfif get_news2.iid NEQ "">
<tr>
<td bgcolor="#class#" width="110"><a
href="index.cfm?fuseaction=home.load_editor&pgid=# pgid#"><span
class="text"><strong>#pg_name#</strong></span></a></td>
<td align="left" bgcolor="#class#" >Available for editing</td>
<td align="right" bgcolor="#class#">
<!-- Actions Area -->
</td>
</tr>
<cfelse>
<tr>
<td colspan="3">There are no pages created for this publication yet</td>
</tr>
</cfif>
</cfoutput>
</cfoutput>
</table>