1 Reply Latest reply on Jun 15, 2007 1:32 PM by Newsgroup_User

    A many-to-many relational problem (SQL and CFM)

    sjlsam2
      My question:
      How to get CFM to return a many-to-many relationship in one row using cfloop

      My table structure:

      Table A - Books
      -------------------
      BookID | BookName
      -------------------
      1 | Book One

      Table B -
      RelatingTable
      -------------------
      BookID | AuthorID
      -------------------
      1 | 60
      1 | 61

      Table C - Authors
      -------------------
      AuthorID | AuthorName
      -------------------
      60 | Bob
      61 | Joe


      My query:

      SELECT * FROM Books, RelatingTable, Authors AS a
      INNER JOIN Books AS b ON b.BookID = r.BookID
      INNER JOIN RelatedTable AS r ON r.AuthorID = a.AuthorID


      Output I am getting:

      -------------------------------------------------------------------------
      b.BookID | b.BookName | r.BookID | r.AuthorID | a.AuthorID | a.AuthorName
      ---------|------------|----------|------------|------------|-------------
      1 | Book One | 1 | 60 | 60 | Bob
      1 | Book One | 1 | 61 | 61 | Joe
      -------------------------------------------------------------------------


      I am using a UDF that turns my relationship into a comma list (authorlist), but the duplicates still return in CFM because the JOIN relationship

      The code I am using in CFM:

      <cfloop query="rsBooksQuery">
      #b.BookName#, written by #authorlist#
      </cfloop>


      How Coldfusion is displaying my output:

      Book One, written by Bob, Joe
      Book One, written by Bob, Joe


      How I want my output displayed:

      Book One, written by Bob, Joe


      I need this to work in cfloop and not cfoutput! I know that you can use group in CF output, but for the conditions I am using this query, it must be in a loop

      the reason why i keep the JOINs even though i have a UDF to create a comma list is that some of my CFM pages use variables passed to the qry to limit which books are displayed, for example &author=60 (which would display a list of Bob's books that include the comma list)

      If you can suggest anything to help me I will be very thankful
        • 1. Re: A many-to-many relational problem (SQL and CFM)
          Level 7
          I need this to work in cfloop and not cfoutput! I know that you can use
          group in CF output, but for the conditions I am using this query, it
          must be in a loop

          If you can suggest anything to help me I will be very thankful


          If you can not use <cfoutput...> with its group feature, you need to
          recreate the functionality with <cfloop...>. You can create nested
          <cfloop...> tags that keep track of the changing group value. It takes
          more code, but that's what happens when one sets outside the bounds of
          the built in functionality.