JOIN or two SELECT statements?

[new7media]

A question about joining more than 2 tables. Here's a simple example of what I'm trying to achieve.

I have three tables in my MySQL dbase that hold data pertaining to news stories. Here's the idea for the tables:

Authors
AuthorID (primary key)
Name

Stories
StoryID (primary key)
Title
Content
AuthorID

Links
LinkID (primary key)
StoryID
* URL

I want to display this data like so:

$Title
by $Name
$Content
See Related Sites:
* $URL

I've set this up in three tables because I figure there will be many stories for any one author, and there could be many (or no) related links for any given story.

To get everything out of the dbase I'm using this (shortened) MySQL select command:

SELECT * FROM Authors, Stories LEFT JOIN Links on Links.StoryID = Stories.StoryID WHERE Authors.AuthorID = Stories.AuthorID

Everything works fine where the Authors and Stories tables are concerned. But the Links table just won't cooperate. What's happening is if there is more than one link for a story, the story prints out again as a separate story and the next link prints out. So if I have two links for a story, I get:

Here's the Story
by Julie Poolie
This story is very short. This is it.
See Related Sites:
* Visit www.link1.com for more info

Here's the Story
by Julie Poolie
This story is very short. This is it.
See Related Sites:
* Visit www.link2.com for photos

What I am after is this:

Here's the Story
by Julie Poolie
This story is very short. This is it.
See Related Sites:
Visit www.link1.com for more info
Visit www.link2.com for photos

I'm wondering if there's some key I'm supposed to be using to do this correctly? Like should I set up the StoryID in the Links table so that it is a "foreign key" or something? If so, how do I call it? Or do I have to do two select commands on the database -- the first to get the story/author info including the StoryID and then a second that selects all records in the Links table that correspond to the StoryID from the first select?

Any help is MUCH appreciated! Thanks!

[Lars_Berg]

That's how a join works. Your setup is correct. It is easiest to handle this with some break logic in the client.

$previous_title = "";
while ($row = mysql_fetch_array($handle)) {
   if ( $previous_title != $row["title"] ) {
// display title and rest of story data
      $previous_title = $row["title"];
   }
//display link
}

You should add an

order by title 

to your query

[mikesgt2]

I think it would be easier and make for more readable code to have a separate query to get the links. You can join the authors and stories and then query within the loop for the links.