Need help with query, quite a complicated one too.

[WilsonC]

have 4 tables with the following fields:
Story:
StoryID
Title
StoryText
Summary
AuthorNo

Author:
AuthorNo
AuthorID

Comments:
StoryID
CommentNo
Comment

I want to get the values of:
StoryID, Title, Summary, AuthorID, Count(CommentNo WHERE Comments.StoryID=Story.StoryID) as 1 row.
The count being the "number of comments the story has".
I have nearly no idea where to start. My only problem is the Count(comments) part. Any help will be appreciated, hope it was not too confusing for anyone to understand.

[jvrothjr]

[code=php:0]
<?
echo "<table border=1 align=center><tr>";
echo "<td align=center>StoryID</td>";
echo "<td align=center>Title</td>";
echo "<td align=center>Summary</td>";
echo "<td align=center>AuthorID</td>";
echo "<td align=center>Row Count</td>";
echo "</tr>";
$querystring = "select distinct StoryId from Story";
$SearchMatch = mysql_query ($querystring);
if ($MatchRecord = mysql_fetch_array($SearchMatch)) {
do{
$querystring1 = "select distinct StoryID from Story";
$SearchMatch1 = mysql_query ($querystring1);
if ($MatchRecord1 = mysql_fetch_array($SearchMatch1)) {
do{
$querystring2 = "select count(*) from Comments where StoryID = ".$MatchRecord['StoryID'];
$SearchMatch2 = mysql_query ($querystring2);
    $Commentcnt=mysql_result($SearchMatch2,0,"count(*)");
echo "</tr>";
echo "<td align=center>".$MatchRecord['StoryID']."</td>";
echo "<td align=center>".$MatchRecord1['Title']."</td>";
echo "<td align=center>".$MatchRecord1['Summary']."</td>";
echo "<td align=center>".$MatchRecord1['AuthorID']."</td>";
echo "<td align=center>".$Commentcnt."</td>";
echo "</tr>";
}
while($MatchRecord1 = mysql_fetch_array($SearchMatch1));
}
}
while($MatchRecord = mysql_fetch_array($SearchMatch));
}
?>
[/code]

[shoz]

[code]
SELECT
s.StoryID, s.Title, s.Summary, a.AuthorID, COUNT(comments) AS num_comments
FROM
Story AS s
INNER JOIN
Author AS a
ON s.AuthorNo = a.AuthorID
LEFT JOIN
Comments AS c
ON
s.StoryID = c.StoryID
GROUP BY
s.StoryID
[/code]

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
http://dev.mysql.com/doc/refman/4.1/en/join.html

[arianhojat]

is CommentNo some autoincrement primary field just to identify the comment?
just curious: with Author, usually the JOIN ONs are done of the primary field aka a field people usually name _____ID, not AuthorNo, is AuthorNo a autoincrement ID or is that AuthorID?

anyway if CommentNo is some field saying the current comment's number like so for Story 2:
2 1 'comment 11'
2 2 'comment 222'
2 3 'comment 33333'

why need the count of it? maybe u just want the COUNT of the rows with same StoryID (, not the same CommentNo).

this maybe put u on right track:
SELECT s.StoryID, Title, Summary, AuthorID, Count(s.StoryID)  FROM Story s
LEFT JOIN Author a ON s.AuthorNo=a.AuthorNo
LEFT JOIN Comments c ON s.StoryID=c.StoryID
GROUP BY s.StoryID

[WilsonC]

Thanks.... that certainly got me on the right track.... but what if i needed to use MATCH and AGAINST fulltext index searches on that query? WHERE should the MATCH and AGAINST be located?

[shoz]

[code]
LEFT JOIN
Comments AS c
ON
s.StoryID = c.StoryID
WHERE
MATCH() AGAINST()
GROUP BY
s.StoryID
[/code]

[WilsonC]

What would be the most efficient way of getting the number of rows that will be returned by that?

[shoz]

[code]
SELECT COUNT(DISTINCT s.StoryID) AS num FROM ...
echo $row['num'];
[/code]

Remove the GROUP BY