Add Comment

Query of Query example.

ColdFusion Tutorial #32

Here's the problem. The table in our database looks like:
Example A - Type 1
Example B - Type 1
Example C - Type 1
Example D - Type 2
Example E - Type 2
Example F - Type 2
Example G - Type 3
Example H - Type 3
Example I - Type 3

But our client/boss/customer wants to display the data on the web page like this:

Type 1: Example A, Example B, Example C

Type 2: Example D, Example E, Example F

Type 3: Example G, Example H, Example I

So how do we get from table to page? It's honestly not difficult in hindsight, but I had to combine a few items I don't often use in my daily routine to accomplish it

demo.cfm

The first step is to get the data out of the table so we can manipulate it. Let's start with a simple Master Query.
Lines 1 - 4

Now that we've created a recordset with our results, we can use the Query of Queries feature, to create smaller record subsets, based upon the value contained in Level.
Lines 5 - 26

We've almost accomplished our task. We've got the results split into smaller recordsets by level. All we need is to display our results! But we can't just drop them into a typical CFOUTPUT tags with query attributes that matches our subqueries, like so . . .
Line 28 - 30

. . . because, as any fool can plainly see, we'll wind up having an extra comma trailing after the last item! However, we can nest a valuelist function (which turns one column of a recordset into a list, separated by the delimiter of our choice) inside a "plain" cfoutput tag:
Line 32 - 41

There are seveal different ways to accomplish this same task but this is a good demonstration of Query of Query in action.

Demo

See this code running!

Download

Download this code as a zip!

Comments

There are no comments!

Click button to add a comment

Author

Jonah Chanticleer

Published

Monday 23 Mar 2009

Original

This tutorial has been modified and published with permission of the author. The original tutorial can be found here
http://anerroroccurred.blogspot.com/2009/03/heres-problem-table-in-our-database.html