1 Reply Latest reply on Apr 10, 2007 12:21 PM by Dan Bracuk

    all possible combinations of a column as applied to a unique entry?

    sjlsam2
      i am having trouble getting a loop to do what i want, if you can help me find out what i am doing wrong i would appreciate it:

      i have a database with 3 tables in it

      table 1 has a list of documents - each one having a primary key
      table 2 has a list of document properties, i will call them property A, B, and C - each one having a primary key
      table 3 is a "relational" table that has a column for a document's PK and a property's PK that that document has

      Example of table 3:
      docPK / propPK
      1 (Document 1) / 1 (A)
      2 (Document 2) / 1 (B)
      3 (Document 3) / 2 (B)
      1 (Document 1) / 2 (B)
      1 (Document 1) / 3 (C)

      i need to create a loop in ColdFusion that spits out the number of possible combinations that exist for each document

      so the correct output should look like:

      Property A
      Document 1

      Property B
      Document 1
      Document 2
      Document 3

      Property C
      Document 3

      Property A, Property B
      Document 1

      Proberty A, Property B, Property C
      Document 1

      this output displays all possible combinations of properties under the conditions of existing documents

      here is the loop i have so far that does not seem to be working for me,

      <cfoutput query="rsProperties">
      <ul>
      <cfloop from="0" to="# of properties per document" index="i">

      <li>
      <cfoutput group="propPK">

      <cfif i EQ "# of properties per document">
      [#Trim(propertyName)#]
      </cfif>

      </cfoutput>
      </li>

      <ul>
      <cfoutput group="docPK">
      <li><a href="">#Document Name#</a></li>
      </cfoutput>
      </ul>

      </cfloop>
      </ul>

      </cfoutput>

      my loop returns possible combinations, but it is not returning ALL possible combinations, for example it may only return Property A as a single instead of also returning singles Property B and C

      my query simply consists of a SELECT * FROM [the database] WHERE [the three tables are set equal (as a join)] AND WHERE [documents exist]


      i know this is all rather confusing but if you can help me make sense of it i will be grateful
      thanks for your time