Solved

Assigning new value to column based on current query

Posted on 2003-12-07
3
149 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
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
jyokum earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IIS 8.5 (Windows 2012 R2) Problems Browsing Virtual Directories 15 108
Intranet Solution - Sharepoint Foundation or up 4 60
htaccess restrict subdomain 4 84
PHP Register global 21 77
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

912 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now