I have a database with three tables. They contain the following relevant fields:

stories:
sid (unique story ID#)

story_author:
sid (relates to the stories table - will not be unique)
uid

authors:
uid (unique Author ID, relates to story_author table)
aname (text for the name of the author)

A story may have more than one author, such that story #1 may have two entries in the story_author table.

Sample information:
stories
sid sname
1 Our First Story
2 My Loner Story

story_author
sid uid
1 1
1 2
2 2

authors:
uid aname
1 Joe
2 Bob

My Problem: I wish to produce, in a single row, both authors of the multi-author story, such that the data might look like this:
sid sname aname
1 Our First Story Joe, Bob

Is this even possible? If so, HOW???

Thanks for any help,
James

I don't think you can do that in MySql as it does not support functions as MSSQL does.

Anyways you can take a look at this post to have an idea.
http://p2p.wrox.com/topic.asp?TOPIC_ID=1787
I made you table structure look similar to what it shows in the above post. But unable to proceed from there as the post deals with Functions there on, which is not supported in MySql(AFAIK)

SELECT a.sid, b.sname, c.aname
FROM story_author a, stories b, authors c
where a.sid=b.sid and a.uid=c.uid

Sid Sname aname
------------------------------
1 Our First joe
1 Our First Bob
2 Lone Story Bob


Also you can take a look at the cross-tab report at MS site, to see if that suits you. I tried this, but didnot work as I expected, may be I am doing something wrong.

http://msdn.microsoft.com/library/de...qd_14_04j7.asp

Let us know if you get it solved.

Cheers!

-Vijay G

I would suggest you to do this from your client application/front end, which would be easier and better way to get that result.

-Vijay G

Yes, turned out doing it from the front end (which happens to be PHP in this case) turned out to be the simplest way to get the desired result (not that getting that result was by any means simple!)

For the curious, I ended up defining a class to hold story information and then filled in each object's information with the query results, using object functions to return the desired output formatting.

Thanks for the assist... at least I didn't waste more time looking for a solution that wasn't there.

James