php inner join

[chriscloyd]

I have two results could 

$result = mysql_query("select CommentID, CommentName, CommentLikes, CommentAuthor, CommentDate from Comments where BlogID = '{$blogID}' and SubComment = '0' and Access >= '{$User->info['Access']}' order by {$sort} limit {$limit}");
		

$result2 = mysql_query("select CommentID, CommentName, CommentLikes, CommentAuthor, CommentDate from Comments where SubComment = '{$cline['CommentID']}' and Access >= '{$User->info['Access']}'");
			

can i inner join to get the sub comments right after the main comments, even though they are in the same table?

 

[jazzman1]

Try,

$sql = "select CommentID, CommentName, CommentLikes, CommentAuthor, CommentDate from Comments where 
BlogID = '{$blogID}' AND CommentID IN (
select CommentID from Comments where 
SubComment = '{$cline['CommentID']}' and Access >= '{$User->info['Access']}')";

[Barand]

You can join a table to itself by using table aliases EG

SELECT cat.name, sub.name
FROM category cat
    INNER JOIN category sub ON cat.id = sub.parent_id