Multiple Left Join Issue

Hi. Friends.

i have three table structure as follow.
message

id message
1 Hello1
2 Hello2
3 Hello3

comment
cid cmessage parent_id
1 comment1 1
2 comment2 1
3 comment3 3

scomment
sid smessage parent_id
1 scomment1 1
2 scomment2 1
4 scomment3 3

Query
SELECT DISTINCT message.id, message.message, comment.cmessage, comment.cid, scomment.sid
FROM message
LEFT JOIN COMMENT ON comment.parent_id = message.id
LEFT JOIN scomment ON scomment.parent_id = message.id

Result.
id message cmessage cid sid

1 Hello1 comment1 1 1
1 Hello1 comment1 1 2
1 Hello1 comment2 2 1
1 Hello1 comment2 2 2
2 Hello2 NULL NULL NULL
3 Hello3 comment3 3 4

problem is it show repeat result. i’m mean comment 1, comment 2 repeat 2 times and sid repeat 2 times.

how it will be handle.

depends what you want to leave out :slight_smile:

if you want only one row returned for each message, why are you joining to those other tables?

note, DISTINCT won’t do the job

i want one message have multiple comment and sub comment… i’m not expert in sql query. can u help.

your original query does this

to print them out the way you want, use your application language (php or whatever)

:slight_smile:

still not success. here is my php code.


SELECT DISTINCT message.id, message.message, comment.cmessage, comment.cid, scomment.sid
FROM message
LEFT JOIN COMMENT ON comment.parent_id = message.id
LEFT JOIN scomment ON scomment.parent_id = message.id

$result = mysql_query($sql) or die(mysql_error());
	
	
	while($rows = mysql_fetch_row($result))
	{
		for($i=1 ; $i<2 ; $i++)	
		{
			$arr[] = $rows ; 
		}
	}
	
	$items = array() ;
	foreach($arr as $row)
	{
	  $id 		= $row[0] ;
	  $message	= $row[1] ;
	  $comment 	= $row[2] ;
	  $cid 		= $row[3] ;
	  $sid		= $row[4] ;
	  
	  if(isset($items[$id]))
	  {
	  	$item = $items[$id];
	  } 
	  else
	  {
	    $item = array("id" => $id,"message" => $message,"comments" => array(),"scomment" => array());
	  }
	  
	  $item["comments"][] 	= array("cid"=>$cid,"comment"=>$comment) ;
	  
	  $item["scomment"][]		= array("sid"=>$sid) ;
	  
	  $items[$id] = $item;
	}
	

	//echo $json ;
	echo '<pre>' ;
	echo print_r($items) ;
	echo '</pre>' ;


i’ve flagged this thread and have asked for a moderator to move it to the php forum

can someone suggest. how to remove duplicate result.

So… what’s the difference between a comment and a sub comment?
Is there a reason you’re storing all these things in an array?
Your query needs ORDER BY clauses. (tsk tsk r937, sleeping at the switch! :wink: )

Anyway…using your array…


foreach ($arr AS $row) {
  if($row['message_id'] != $curmsgid) {
    //Original Message Info Goes Here.
    $curmsgid = $row['message_id'];
  }
  if($row['cid'] != $curcomment) {
    //comment Info Goes Here.
    $curcomment = $row['cid'];
  }
  //Subcomment Info goes here.
}

Thanks StarLion, yes difference between comment and subcomment is, one message have multiple comment and one comment have multiple sub comment. i using array for convert it to JSON format.

okay, then you need to adjust your query and table structure to left join subcomment to comment, rather than subcomment to message.