Articles db; display one row for multiple authors

I am looking to display a database of back issues of a print magazine and the articles in those back issues on my Joomla 1.5 site. I'm pretty certain that Card View JX will do what I want, except for one thing.

Please see attached image of a screenshot of the first page from phpMyAdmin of the db. You'll note on lines 5 and 6 and 12 and 13, there are two authors for a single article. How do I get the two authors to display under a common line that includes `title` and `page`?

I'm looking for output something like:

*Page 18, Setting up at a train show made them steaming, By Scott E. McDonald.
*Page 20, Series wraps up with a look at sand boxes. By Dan Rowe.
*Page 25, The Aster Alishan Shay. By Ross Schlabach.
*Page 33, Building a steam-powered Model-T rail truck. By Howard Maculsay.
*Page 40, N&W Class J No. 611 electric-to-steam conversion. By Charles Bednarik and Ryan Bednarik.

Tomaz:

Thanks for your response. Yes, I can create views.

I had used that SQL statement (just in the phpMySQL window, not in a view) before and that does not get me both of the author names ... it does eliminate the second iteration of the title, but the second set of author names is not listed.

I tried it again just now and it doesn't work.

Do you have any other thoughts?

\dmc

Tomaž:

Thanks for working on my problem. Your solution works except for some hiccups:

*We have instances where a father and son are co-authors, and so the fields author_last are equal, which means the code doesn't work at all. If the co-authors have different last names, it works, inserting the second author's name into the new fields 2nd_author_last and 2nd_author_first.

*Sometimes the titles are the same across multiple issues (hadn't noticed this myself until just how); so the equivalence needs to be both title and issue.

*The second author's record is still displayed (should be suppressed somehow).

I guess some pseudo code might read like

If title and issue are equal and both author_first and author_last aren't equal, then put a second author's name into 2nd_author_first and 2nd_author_last and eliminate this record from displaying at all.

I have queried a number of different SQL experts on this problem and you've come the closest of any of them, so you're on the right track.

I appreciate your help.

\dmc