Skip to main content
A
Anonymous
March 15, 2011
Question

Compound Query?

  • March 15, 2011
  • 1 reply
  • 476 views

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!

    This topic has been closed for replies.

    1 reply

    ilssac
    ilssac
    Inspiring
    March 15, 2011

    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.

    IDCATEGORY
    		ITEM
    1522
    1526
    1528
    1529

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

    IDCATEGORY
    152

    ID

    		ITEM
    12
    16
    18
    19


    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".

    A
    Anonymous
    March 15, 2011

    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
    ilssac
    Inspiring
    March 17, 2011

    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 )
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices