php - MySQL SELECT to subquery a many-to-many relationship -

i have bilingual dictionary database i've created, , tables set so:

lemma (lemmaid, lemma, meaning)  collocate (collocateid, lemmaid, collocate, notes, connection)  collusage (usageid, lemmaid_u, collocateid_u, japanese, english, englishalt)  partofspeech (posid, partofspeech)  postolemma (lemmaid_p, posid_p) 

so far, have query returns tables results, , works how i'd to. (it looks like this)

$q = 'select * lemma, collocates, collusage lemma.lemmaid = collocates.lemmaid , lemma.lemmaid = collusage.lemmaid_u , collusage.collocateid_u = collocates.collocateid order lemma.lemmaid;';  $result = mysqli_query($con, $q) or die(mysql_error());  if (!$result || mysqli_num_rows($result) == 0) {     echo 'no rows found';     exit; }  $lastcatid = 0;  while ($row = mysqli_fetch_assoc($result)) {     $reading = $row['reading'];     $headword = $row['lemma'];      $collocate = $row['collocate'];      if (isset($row['notes'])) {         $notes = '&lpar;'.$row['notes'].'&rpar;';     } else {         $notes = $row['notes'];     }      $japanese = $row['japanese'];     $english = $row['english'];      if (isset($row['englishalt'])) {         $englishalt = ', '.$row['englishalt'].'';     } else {         $englishalt = $row['englishalt'];     }      if ($lastcatid != $row['lemmaid']) {         //starting new category         if ($lastcatid != 0) {             //close previous table             echo '    </tbody>             </table> </div>';         }         //start new div                 echo '<div class="entry">                 <h4>'.$reading.'【'.$headword.'】 <span class="pos">'.$want list parts of speech here.'</span></h4>                 <table class="table table-striped table-hover">                      <tbody>';         $lastcatid = $row['lemmaid'];     }      echo '<tr>             <td><span>'.$collocate.'</span><span class="notes">'.$notes.'</span></td>             <td>'.$japanese.'</td>             <td>'.$english.''.$englishalt.'</td>             </tr>'; }          if ($lastcatid != 0) {             //close final table             echo '    </tbody>             </table></div>';         }          mysqli_free_result($result); 

what can't figure out how use postolemma junction table of partofspeech values each lemmaid can list them next lemma in table. select query have done far has duplicated collocation entries, don't want. appreciated!

edit: here link sql fiddle data. couldn't foreign key constraints work missing.

if understand correctly want select values table partofspeech based on lemma table. query should this:

select part.partofspeech  partofspeech part inner join postolemma post     on part.posid = post.posid_p inner join lemma l     on post.lemmaid_p = l.lemmaid 

also suggest change query use , start using join operator in syntax, it's practice , it's not hard switch 1 another... query:

select * lemma, collocates, collusage lemma.lemmaid = collocates.lemmaid        , lemma.lemmaid = collusage.lemmaid_u        , collusage.collocateid_u = collocates.collocateid order lemma.lemmaid; 

will this:

 select *   lemma  inner join collocates      on lemma.lemmaid = collocates.lemmaid  inner join collusage     on collusage.collocateid_u = collocates.collocateid     , lemma.lemmaid = collusage.lemmaid_u  order lemma.lemmaid; 

also can use aliases table in query in first query wrote here. make life easier because don't need type whole name of table on , over...

gl!

p.s. it's post desired result in question , provide sql fiddle data our better understanding of queston...

edit

after consulted in comments come solution:

 select *   lemma  inner join collocates      on lemma.lemmaid = collocates.lemmaid  inner join collusage     on collusage.collocateid_u = collocates.collocateid     , lemma.lemmaid = collusage.lemmaid_u  inner join (select post.lemmaid_p lemmaid, group_concat(part.partofspeech separator ', ') partofspeach              partofspeech part              inner join postolemma post                on part.posid = post.posid_p              inner join lemma l                on post.lemmaid_p = l.lemmaid              group post.lemmaid_p) tmp  on lemma.lemmaid = tmp.lemmaid  order lemma.lemmaid; 

here sql fiddle that...