Compound Query?

[Guest]

Up until now my experience with Coldfusion has been simple queries that I return to Flash. I have a project where I get "designs" from the database and each design has a string of "design_items" that make up the design. So a design result would look like this:

result[0].id = 1

result[0].category = 52

result[0].items = "2,6,8,9"

Then I use the items result to get the details of each piece of the design. Each design_item has 30 plus bits of info it returns. So as you can see not efficient at all. I sometimes make 200 calls to coldfusion to just build a few designs to display to the user. How can I do this:

Get the designs based on the category.

Make the list of items into an array or something that I can cycle through and get the design_items info for?

So my result would look something like this

result[0].id = 1

result[0].cateogry = 52

result[0][0].items = design_item_id = 2

                            design_item_name = "text"...

result[0][1].items = design_item_id = 6

                             design_item_name = "graphic47"

I would like to cut down my calls to coldfusion to one. I just don't know how to really start.

Thanks!

[ilssac]

You are struggling with what LOOKS like a poorly normalized data design in your database.

You SHOULD NOT be returning a list as your items value.

You SHOULD be returning multiple rows each with one item.

ID	CATEGORY	ITEM
1	52	2
1	52	6
1	52	8
1	52	9

You acheive this by have two related tables in your relational database (Get the reference?)

ID	CATEGORY
1	52

ID	ITEM
1	2
1	6
1	8
1	9

You can then join them with simple syntax like this.

SELECT
 a.ID
 a.CATEGORY
 b.ITEM
FROM
 aTable a INNER JOIN
 bTable b ON a.id = b.it.

For futher explanation for all this, I have heard good things about the books "Database Design for Mere Mortals" and "Teach Yourself SLQ in 24 Hours".

[Guest]

I actually did not build the database and changing it is not an option. This company has been using the same data for 10 years and it's so large they cannot af

fort to rebuild it.

Carey

[ilssac]

Then you probably want to look into the "IN" SQL clause.  It allows you to select records based on a list of values.

You may also want to look into using SUB SELECT queries to build part of the larger query.

IE. Something like this:

SELECT aField, bField, cField
FROM aTable
WHERE idField IN (
     SELECT idField
     FROM idTable
     WHERE something = somethingElse )