php - SQL Left join Many to Many, only display distinct ID in left table -

tablea

id name 1   peter 2   mary 3   john 

tableb

id event 1  eventa 2  eventb 3  eventc 

tablec

id aid bid 1  1 1 2  1 3 3  2 1 

i doing this

select distinct a.id, b.id tablea left join tablec c on a.id = c.aid left join tableb b on c.bid = b.id 

it shows

1 peter eventa 1 peter eventc 

i want output follows

1 peter eventa         eventc 

how that? many thanks.

---

amendment:

actually displaying records below:

1 peter
eventa
eventc

from suggestion works fine, wanna add hide show function.

function reversedisplay(d) {

if(document.getelementbyid(d).style.display == "none") {

document.getelementbyid(d).style.display = "block"; }

else { document.getelementbyid(d).style.display = "none"; }

}

select distinct a.id, b.id, b.event

from tablea a

left join tablec c

on a.id = c.aid

left join tableb b

on c.bid = b.id

$lastid = ""; while($row = mysql_fetch_array($rs)) {        if ($lastid != $row[0]){         echo "<a href='javascript:reversedisplay(" . ($row[0]) . ")'>" . $row[0] . "</a>";           echo urldecode($row[1])." ";             $lastid = $row[0];         echo "<br/>";          //id needs unique match javascript function         echo "<div id=" . ($row[0]) . " style='display:none;'>";     }      echo $row[2];         //that's problem, </div> must print 1 time when in last loop of same a.id, how???     echo "</div>"     } 

if add count function

select distinct a.id, b.id, b.event, count(a.id)

from tablea a

left join tablec c

on a.id = c.aid

left join tableb b

on c.bid = b.id

it shows

1 peter

eventa

// not show eventc

// loops 1 time

could help? have tried days failed. many thanks.

this output (html) formatting issue, not sql issue. (you theoretically construct complicated query using subqueries, cases , nulls, there's no need go trouble.)

if execute query (note order by):

select distinct a.id, a.name, b.event tablea left join tablec c on a.id = c.aid left join tableb b on c.bid = b.id order a.id 

then in php:

// i'm assuming you're using mysqli in oo fashion, // can adjust needed.  also, i'm missing out // html (except <br>) - can add in whatever // html formatting require per desired layout $lastid = null; // remember last person id had while ($row = $result->fetch_assoc()) {     if ($lastid != $row('id') {         // different person, show name:         echo $row('name');         // , update $lastid          $lastid = $row('id');     }     // show event , move next line:     echo $row('event') . '<br>'; }