Multiple Left Join Issue

PHP
1

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.

2

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

3

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

4

your original query does this

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

:slight_smile:

5

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>' ;
6

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

7

can someone suggest. how to remove duplicate result.

8

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.
}
9

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.

10

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