Sorting on results from many-to-many relationships

[profwannabe]

I have tried to resolve this problem both using SQL and using various CF tricks to no avail. I am running an Access DB on a CF 4.5 platform.

I am generating a bibliography from a database. When a subject is selected (SubjectID) a list of books is generated using a subquery, as there is a many-to-many relationship between BiblioID and SubjectID. What I need to do is sort this list of books by the last name of the first listed author.

Some books have multiple authors, so I have a many-to-many relationship established (Tables: Biblio, Book_Author_Relationship, Authors). The problem is that I need to sort on the first author for each book (note: authors are not always listed alphabetically; there is a BAID primary key in the relationship table so that I can sort authors in order of entry rather than by authorID). For my output I then requery to get the full list of authors for each BiblioID. Although I have tried a variety of approaches, I cannot sort by AuthorLastName without getting multiple entries for books with multiple authors.

I have tried using SQL commands such as Group By, Distinct, and DistinctRow on Book_Author_Relationship.BookID with no luck. I have also tried to use the CF command Group, but that applies only after the query has sorted. I have tried creating an array of results from the Book_Author_Relationship table excluding multiple entries for a single book (due to multiple authors) using <cfif> but cannot think of how to then use that array for my output (since I need to requery to get the additional authors).

Not sure if CF or SQL is the best solution to this problem. Any advice would be greatly appreciated :-(

 

[SidianConsulting]

I sort-of know what you want to do, but can you please post the 3 tables' structures and then a description of how the returned data should look? That would help out a lot to give you an accurate answer.

-Tek

 

[profwannabe]

Okay,

First, the output is simply a list of bibliography entries that need to be sorted by AuthorLastName: AuthorLastName, AuthorFirstName, SecondAuthorFirstName SecondAuthorLastName,... Year. Title. PublisherLocation: Publisher. This list of entries is generated by selecting a subject.

Here are the tables involved:
Biblio (BiblioID, Year, Title, PublisherID)
Biblio_Subject_Relationship (BiblioID, SubjectID)
Biblio_Author_Relationship (BAID, BiblioID, AuthorID)
** I use an autogenerated PK rather than just the unique combination of the two IDs here because I need to know the order in which authors were input. Frequently, multiple authors are not alphabatized so this way I can sort by BAID to get the correct sequence **
Authors (AuthorID, AuthorLastName, AuthorFirstName)

Currently I do the following: Select * From Biblio INNER JOIN Publishers ON Publishers.PublisherID = Biblio.PublisherID WHERE BiblioID IN(Select BiblioID from Biblio_Subject_Relationship where SubjectID = #url.SubjectID#). This gives me all the Biblio entries to build the list as well as the publisher information from a separate table.

I then use a cfloop to output the biblio info. Within the loop I query: select * from Authors INNER JOIN Biblio_Author_Relationship ON BAR.AuthorID = Authors.AuthorID ORDER BY BAID. This lets me output the authors in the correct order.

What I need to do, essentially, is have a join in my initial query that gets me the first record from the Book_Author_Relationship table for BiblioID. I can then join AuthorLastName to this and sort on AuthorLastName so that my entries are sorted by author. However, I have tried a variety of things to get only the first record, to no avail.

I won't even try to figure out how to avoid using the cfloop and instead append all the authors' names to the biblio entry in order, in part because the output changes after the first author (LastName, FirstName becomes FirstName LastName, with an and before the last author in the list).

Hope that makes things a little clearer!

 

[profwannabe]

Tek,

What works, of course, and I am doing currently is that in the Biblio table I give each entry a PrimaryAuthorFirstName and a PrimaryAuthorLastName. This works fine for the initial sort, but I am replicating data so it seems that there should be a better way, no?

Thanks anyway for your interest.

 

[SidianConsulting]

I can work on this at the end of the day; if someone else chimes in with the answer before then, more power to them. Just wanted to let you know I didn't forget about it!

-Tek