MS SQL query within a query ColdFusion environment

Question

I have 2 tables, one is called Food while the other is FoodCategory. The relation between them is the FoodCatID which contain in both tables.

What I try to archive is displaying:

Meat

Chicken
Beef

Veg

tomatoe
potatoe

I assume I will need a query within a query? I first try to use Distinct to get the 2 unique FoodCatID:

Select Distinct FoodCategory.FoodCatID, FoodCategoryName
From Food INNER JOIN FoodCategory ON Food.FoodCatID = FoodCategory.FoodCatID

This will give me the 2 categories, but then how can I use the CatID to run the second part of the query?

I'm using this on a ColdFusion page, should I archive the result using SQL queries or can I do it through CF code?

Leigh · Accepted Answer · 2013-08-30 04:40:41Z

2

should I archive the result using SQL queries or can I do it through CF code?

Both. Use a single JOIN to retrieve the categories and food names. SQL Fiddle

SELECT fc.FoodCatID
       , fc.FoodCategoryName
       , f.FoodID
       , f.FoodName
FROM   FoodCategory fc INNER JOIN Food f ON f.FoodCatID = fc.FoodCatID
ORDER BY fc.FoodCategoryName, f.FoodName

Then use a "grouped" cfoutput. to list all of the foods - but only display the category headers once.

Note, the results must be ordered by category name first, or it will not work

  <cfoutput query="yourQuery" group="FoodCategoryName">
      <!--- display header once -->
      #FoodCategoryName#<br><br>
      <cfoutput>
         <!--- display all foods --->
          #FoodName#<br>
      </cfoutput>
  </cfoutput>

answered Aug 30, 2013 at 4:40

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS SQL query within a query ColdFusion environment

Meat

Veg

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Meat

Veg

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related