Skip to main content
O
Olivia_Crazy_Horse
Known Participant
April 13, 2009
Question

ValueList Question

  • April 13, 2009
  • 1 reply
  • 715 views

I need to join a master table to a detail table, there are one to many relationships. So it will give me duplicates. But if I only want everything in the master table and only error_code (example) from the detail, how do I use valulist to put the error on one line, error1, error2, error3, etc, so that there are not three lines ?

Also, when I do my record count, is it going to be 1 (which it should be) or 3, because of the three matches ?

    This topic has been closed for replies.

    1 reply

    ilssac
    ilssac
    Inspiring
    April 13, 2009

    One wouldn't normally consider valueList() for this type of requirement.

    What do you really want?  The ability to display records from your master list with the detail item grouped on one line. (A fairly easy task)

    OR

    A record set that truely combines the detail datum into a single column of the record set. (A more sophisticated solution that will involve advanced SQL techniques and sub queries)

    OPTION A

    <cfquery name="myStuff" datasource="myDSN">
      SELECT master.aField, detail.bField
      FROM master INNER JOIN detail ON (master.key = detail.foreignKey)
      ORDER BY master.aField
    </cfquery>
    <cfoutput query="myStuff" group="aField">
      <p>#myStuff.aField#
      <cfoutput> #myStuff.bField#</cfoutput>
      </p>
    </cfoutput>

    NOTE: The relationship to the ORDER BY clause field and the group field in the outer <cfoutput> tag is very important.  The documentation discuss this in detail.

    O
    Olivia_Crazy_HorseAuthor
    Known Participant
    April 13, 2009

    I tried your example and it seems to work great. One question, though, is how do I put a comma separtor between each multiple entry line ? If I add a comma at the end of the cfoutput, that would also add a comma to single entries also, so I only need the comma separator for the multiple entries.

    D
    Dan_Bracuk
    Inspiring
    April 13, 2009

    You started with something like this.

    <cfoutput query="aQuery" group="aField">

    #grouped data#

    <cfoutput>

    #ungrouped data#

    </cfoutput>

    </cfoutput>

    To get the commas, do this.

    <cfoutput query="aQuery" group="aField">

    #grouped data#

    <cfset ungrouped = "">

    <cfoutput>

    <cfset ungrouped = ListAppend(ungrouped, SomeDataFromYourQuery)>

    </cfoutput>

    #ungrouped#

    </cfoutput>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices