Solved

Assigning new value to column based on current query

Posted on 2003-12-07
3
154 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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
What You Need to Know when Searching for a Webhost Provider
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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