Nested query results

[IanHindle]

Hello

I'm sure this question is quite common, would be very grateful for any
guidance.

I have an access database, which contains two tables: Table 1 is a list of
Servers (IP address of, comments field etc, there is a serverID also, which is
set to primary key). Table 2 is a chatroom table and has fields such as a
comments field, chatroom name (which is primary key), and also a field named
serverID - using Access, I created a relationship between the two serverID
fields.

I now wanted to query the data in the list of servers, and subsequently grab
all the chat rooms associated with that specific server, presenting the results
something like:

IP of server (as a title for example)
chatroom name 1
chatroom name 2
... etc

so I want to dump out on the web page all of the channel names associated with
each server listed in the Servers table (preferably using a nice table format).

What Im confused about is, getting the query correct, and even more confused
about, is outputting the results... so far I think I have the query correct but
I get duplication in the output of the results, for example, if i have 3 chat
channels associated with one Server, the server will be listed three times,
with each chat room name listed next to each - so its almost there, I just want
to bunch all the chat room names under just one title.

thanks
Ian

[Dan Bracuk]

Make sure your query is sorted by your IP address. Then use the group
attribute of the cfoutput tag to format your results. Details are in the cfml
reference manual. If you don't have one, the internet does.

[paross1]

Something like this?

Phil



SELECT s.IPaddress, c.name
FROM chatrom c
INNER JOIN servers s ON c.serverID = s.serverID
ORDER BY s.IPaddress, c.name

<cfoutput query="your_query" group = "IPaddress">
#IPaddress# <BR>
<cfoutput>#name# <BR>
</cfoutput>
</cfoutput>

[IanHindle]

Hi there, thanks for the replies gents. I've made some modifications to the
code, and it still is displaying the results in a multiple fashion, as apposed
to grouped together. Here is the code below:

I've never seen the s. and c. thing much before, Im guessing that is some kind
of aliasing?



<cfquery name="getCurrentlist" datasource="ircservers">
SELECT s.IRCServerIP,
s.IRCServerInfo,s.IRCServerDateTimeAdded,c.IRCchan nelName,c.IRCchannelInfo,c.IRC
channelDateTimeAdded
FROM IRCchannels c
INNER JOIN IRCservers s ON c.IRCServerID = s.IRCServerID
ORDER BY s.IRCServerIP, c.IRCchannelName
</cfquery>



<p><b><font face="Arial">View/Manage List</font></b><br>
&nbsp;</p>

<table border="1" width="100%" id="table1">
<tr>
<td align="left" valign="top"><b>IRC Server IP</b></td>
<td align="left" valign="top"><b>Server Comments</b></td>
<td align="left" valign="top"><b>Associated Channels</b></td>
</tr>
<tr>
<cfoutput query="getCurrentlist" group="IRCchannelName">
<td align="left" valign="top">#IRCServerIP# added on
#IRCServerDateTimeAdded#</td>
<td align="left" valign="top">#IRCServerInfo#</td>
<td align="left" valign="top">

<table border="1" width="100%" id="table2">
<tr>
<td><b>IRC Channel Name</b></td>
<td><b>IRC channel Info</b></td>
</tr>

<tr>
<td>#IRCchannelName# added on #IRCchannelDateTimeAdded#</td>
<td>#IRCchannelInfo#</td>

</tr>

</table>
</td>
</tr>
<tr>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
</tr></cfoutput>
</table>

[Dan Bracuk]

Give your fields aliases in the select clause of your query. Then use those names in cfoutput.

[IanHindle]

hmmm, I just dont get it. I dont know enough SQL to understand aliasing :-S
anyone know where an example set of code is?

[paross1]

I guess that you missed it in my previous example, but you neglected to nest an
additional <cfoutput> tag within the other <cfoutput>. Something like the
attached.

Phil



<table border="1" width="100%" id="table1">
<tr>
<td align="left" valign="top"><b>IRC Server IP</b></td>
<td align="left" valign="top"><b>Server Comments</b></td>
<td align="left" valign="top"><b>Associated Channels</b></td>
</tr>
<tr>
<cfoutput query="getCurrentlist" group="IRCchannelName">
<td align="left" valign="top">#IRCServerIP# added on
#IRCServerDateTimeAdded#</td>
<td align="left" valign="top">#IRCServerInfo#</td>
<td align="left" valign="top">


<tr>
<td><b>IRC Channel Name</b></td>
<td><b>IRC channel Info</b></td>
</tr>
<cfoutput>
<tr>
<td>#IRCchannelName# added on #IRCchannelDateTimeAdded#</td>
<td>#IRCchannelInfo#</td>

</tr>

</cfoutput>
</td>
</tr>
<tr>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
</tr></cfoutput>
</table>

[IanHindle]

I have the additional <cfoutput> tags but the result is still the same: I am getting duplication of the same IP address with one channel name under each instead of one IP address and all chan names

[paross1]

Sorry, change this <cfoutput query="getCurrentlist" group="IRCchannelName"> to this <cfoutput query="getCurrentlist" group="IRCServerIP"> as you need to group on the IRCServerIP field.

Phil

[IanHindle]

Excellent! I understand now, grouping on the actual IP. Thanks phil, this has worked.