MYSQL JOINS

[aclab]

Not entirely sure if this is possible... I've got two queries and I'm not sure if it is possible to merge the two as there is already a JOIN on the first the query...

 

Here is my current code (which works fine):

	$sql = "SELECT p.*, u.username
	FROM posts p
	LEFT OUTER JOIN users u ON (u.id = p.userID)
	WHERE parentID IS NULL
	LIMIT $offset, $resultsPerPage";
$result = mysql_query($sql);
while($record = mysql_fetch_assoc($result))
{
    # Count the replies for the parent message
    $record = $this->cleanArray($record,"load");
    $sql = "SELECT id FROM posts WHERE parentID = " . $record[id];
    $res = mysql_query($sql);
    $replies = mysql_num_rows($res);
        ...
        }

 

...so I'm looking to extend the first statement to also count the number of posts (where the parentID = p.id) for each record where the parentID = NULL

('posts' table contains child and parent records).

Hope that makes sense? Any help appreciated...

Thanks

[lucius]

For counting, use the mysql COUNT function and try the GROUP BY for counting different types. 

 

Maybe:

 

SELECT COUNT(p.id) as totalpid

FROM [...]

WHERE [...]

GROUP BY p.id