Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Assigning new value to column based on current query

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?
  • 2
1 Solution
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

<cfoutput query="qryData" group="topic">
      #qryData.topic#<br />
      <cfif qryData.topic neq qryData.description>
      &nbsp;&nbsp;-&nbsp;&nbsp;#qryData.description#<br />
James RodgersWeb Applications DeveloperCommented:
use group by in your query and in the

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

if that doesn't do what you want then try this
<cfset strTopic="">
<cfoutput query="'>
<cfif strTopic neq Topic>
<cfset strTopic=Topic>
James RodgersWeb Applications DeveloperCommented:
damn you beat me.... lol ;-)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now