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

[Newsgroup_User]

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.