Ms Sql Query Within A Query Coldfusion Environment

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: M

Solution 1:

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
ORDERBY 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

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