sql query and cfoutput

I have a database as follows

ID      acccode          title                               set
1           A                 Header                           T
2           A1               sub heading                    T
3           A1333         company1                        T
4           A1335         company2                        Z

i need to output

HEADER

SUB HEADING

      company 1

I do not output company 2 as its code is Z


so far:
SELECT ACCNT_CODE , ACCNT_NAME
FROM  dbo.dbo_SSRFACC
WHERE SUSPEND <> 'Z'
ORDER BY ACCNT_CODE

doesnt work because of the WHERE clause for some reason, its a varchar

I can use multiple sql statements and loop over the sub departments and companies, can you please help

LVL 1
Ken-dohAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sandy12879Commented:
select  title from tablename  where set ='t'
hartCommented:
how is that u have the main heading, sub heading and companies under these in the same table...

if u want just the first three records then what sandy wrote is fine just change
select  title from tablename  where set ='t'

to

select  title from tablename  where set ='T'
set is a database key word i think ur fieldname is SUSPEND  so it would be suspend = 'T'

or if u want to show the heading first then the sub heading and then list all the companies that come under these sub heading

Q1. will the acccode  for a heading be always 'A'
Q2. will the accode for a subheading be always 'A1'

see i would have kept a dept / heading table [with headingid and heading]
a sub department / sub heading table [with subheadingid and subheading ]

and in the other table i would have stored data as
headingid
subheadingid
company

this way by using joins i would have got the heading, sub heading and companies in one query..

Regards
Hart

3ggCommented:
This should get you started in the right direction. I don't have a debugger here to check this SQL, but you should be able to do this with MS-SQL Server 2000, mySQL and PostgreSQL. Oracle changes the function to SUBSTR()

<cfquery name="q" datasource="yourdatasource">
   SELECT SUBSTRING(acccode,1,1) AS groupA,
                SUBSTRING(acccode,1,2) AS groupB,
                title
   FROM dbo.dbo_SSRFACC
   WHERE suspend <> 'Z'
   GROUP BY acccode, title
   ORDER BY groupA, groupB
</cfquery>

<cfoutput query="q" group="groupA">
 <p>#q.groupA#</p>
 <cfoutput group="groupB">
  <p>#q.groupB#</p>
  <cfoutput>
   #q.title#<br />
  </cfoutput>
 </cfoutput>
</cfoutput>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.