?
Solved

Assigning new value to column based on current query

Posted on 2003-12-07
3
Medium Priority
?
159 Views
Last Modified: 2013-12-24
I'm working with an old database which I have no control over, so I'm stuck to work with it.

There are just three columns: id, topic, and description - designed to be just a single list of topics.  I want to have sub-topics as well and don't need the description field, so I'm using the topic column to store the main topic and the description column to store the sub topics. For main topics, the topic and description are the same.  Hence it looks like this:

id, topic, description
1, New York, New York
2, New York, Manhattan
3, New York, Ithica
4, New York, Long Island
5, California, San Francisco
6, California, California
7, California, Los Angeles

I need the output of the query to be a single list, with the main topics above their associated subtopics. So I was hoping to create a new column (0 if both columns equal, 1 if they don't) so that the output could be ordered correctly. Any idea how to do this? Or is there a better way?
0
Comment
Question by:bududa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
jyokum earned 2000 total points
ID: 9893326
change the datasource and table name to whatever is appropriate.

<cfquery name="qryData" datasource="whatever">
SELECT id, topic, description
FROM tablename
ORDER BY topic, description
</cfquery>

<cfoutput query="qryData" group="topic">
      #qryData.topic#<br />
      <cfoutput>
      <cfif qryData.topic neq qryData.description>
      &nbsp;&nbsp;-&nbsp;&nbsp;#qryData.description#<br />
      </cfif>
      </cfoutput>
</cfoutput>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9893327
use group by in your query and in the

<cfoutput query="" group="topic">
 #topic# #description#
</cfputput>

if that doesn't do what you want then try this
<cfset strTopic="">
<table>
<cfoutput query="'>
<tr>
<td>
<cfif strTopic neq Topic>
   #topic#
</cfif>
<cfset strTopic=Topic>
</cfif>
</td>
<td>#description#</td>
</tr>
</cfoutput>
</table>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9893332
damn you beat me.... lol ;-)
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question