[AwesomezGuy]

You want to use a LEFT JOIN for this, Google it.

[Daniel15]

A left join would work, you'd get a lot of data back from the database though (it'd return duplicates of every status - One for each comment). That's quite a bit of overhead, especially if your database is a separate server to your web server.

Here's how I'd do it with two queries (PHP-ish, I can't remember the exact syntax for PDO queries ):

// First get the statuses
$statuses = array();
$statusResult = $db->query('
	SELECT sid, uid, status, dateline, tweetid
	FROM statuses
	ORDER BY sid DESC
	LIMIT 10');
	
while ($status = $statusResult->fetchObject())
{
	// Create empty array for comments
	$status->comments = array();
	
	$statuses[$status->sid] = $status;
}

// Get the comments for all these statuses
$statusIds = array_keys($statuses);
$commentResult = $db->query('
	SELECT cid, sid, comment, dateline
	FROM status_comments
	WHERE sid IN (' . implode(', ', $statusIds) . ')');
	
while ($comment = $commentResult->fetchObject())
{
	$statuses[$comment->sid]->comments[$comment->cid] = $comment;
}

// $statuses now has every status, with all their comments

The second query gets the comments for just the statuses retrieved.

Then in your view/template file:

foreach ($statuses as $status)
{
	// Output the status
	echo '
	<li>
		', $status->status, '
		...
		<ol class="comments">';
		
	foreach ($status->comments as $comment)
	{
		echo '
			<li>
				', $comment->comment, '
				...
			</li>';
	}
		
	echo '
		</ol>
	</li>'
}

[euantor]

@Daniel: Now, why did I not think of that? Of course that's the best way to do it! Wow. You just blew my mind, literally. Thanks! Sometimes I just see the wood for the trees.