We help IT Professionals succeed at work.

Cfouput distinct items for a field

281 Views
Last Modified: 2008-05-13
I have a table that I want to output into a series of drop down menus.  Each field has duplicate entries that cannot be removed.  Using a query I want to pull all of the data from the table at once.   I only want to display the distinct entries from each field.  I cannot use SELECT DISTINCT in the query though because all entries are distinct.  I need to be able to pull distinct fields and all data at the same time.    I put an example below that I hope explains what my table looks like a little better.

hierarchyID   domain                          directory     subdirectory      subdirectory2
1                   www.mysite.com          about            docs                 images      
2                   www.mysite.com          about            docs                 forms    
3                   www.mysite.com          about            contact
4                   www.mysite.com          forms          
5                   www.mysite.com          forms            images

My query is

<cfquery name="gethierarchy" datasource="Review">
SELECT  *
FROM hierarchy
ORDER BY hierarchyID ASC
</cfquery>

I would like to display the distinct directory, subdirectory,...   For example I'll want to pull only subdirectories in the "about" directory.  As you can see in my example table above that there are duplicate entries under each field but the complete database entry is not a duplicate.   Is there a way to do something like <cfouput query="gethierarchy">DISTINCT(#directory#) so that I can just run the query once but output the distinct items?

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
OK. Here is a little more information. I want to do something similiar to below.  I had to add a line to the query.  Can I use either of your suggestions for this?

<cfquery name="gethierarchy" datasource="Review">
SELECT *
FROM hierarchy
WHERE directory = '#url.formdir#'
ORDER BY hierarchyID ASC
</cfquery>

<cfif subdirectory neq "">
    <cfif subdirectory neq "">
       <select name="subdirectory" size="1">
       <option value="">Select subdirectory</option>
       <option value="#subdirectory#">#subdirectory#</option>
       <option value="#subdirectory2#">&nbsp;&nbsp;&nbsp;#subdirectory2#</option>
       </select>
      </cfif>
  <cfelse>
      <select name="subdirectory" size="1">
      <option value="">Select subdirectory</option>
      <option value="#subdirectory#">#subdirectory#</option>
      </select>
  </cfif>
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
What if instead of using distinct  I were to check that entries are unique and just output each instance once?
I can't recall if I've done that before though.  How do I do that?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.