title: How to perform inner join on a table with one to many relation?
body: <p>This search uses various webpages in a database by matching values of metadata. Here value field has more than one value and I am not sure how to get only GRADUATE Science courses or something similar.
  I have tried so many things but nothing works.</p>
  
  <pre><code>select distinct page.path,page.site_id,
    (select metadata_custom.value AS display_name
     from metadata_custom
     where field='academic search title'
       and page.id=metadata_custom.page_id) AS value,
     page.id
     from publish.page
     inner join metadata
       on page.metadata_id=metadata.id
     inner join metadata_custom
       on page.id=metadata_custom.page_id
     where field='acadsearch'
       and value like ('science%' AND 'graduate%");
  </code></pre>
  
  <p>Also, I have another query where I use
  value like  '{$term}%'</p>
  
  <p>some sample tables
  TABLE PAGE: </p>
  
  <pre><code>id | account_id | site_id |  cms_id  | folder_id |  metadata_id | name |path | content
  ----------------------------------------------------------------------------------------------------
  | 583 | 1 |   1 | e026b376c0a80123019b60bb23817853 | db2b800fc0a80123019b60bbb5e082cd| 2915 | rob-castillo| academics/undergraduate/social-work/rob-castillo | &lt;region-render&gt;&lt;div class="center_text"&gt;
  &lt;content/&gt;
  &lt;/div&gt;&lt;/region-render&gt;   
  ----------------------------------------------------------------------------------------------------
  | 578 |  1 |   1 | e026b25fc0a80123019b60bb58c9f847 | db2b800fc0a80123019b60bbb5e082cd | 2887 | jeffrey-bulanda | academics/undergraduate/social-work/jeffrey-bulanda | &lt;region-render&gt;&lt;div class="center_text"&gt;
  &lt;content/&gt;
  &lt;/div&gt;&lt;/region-render&gt; 
  
  Table Metadata Custom
  
  id | account_id | site_id | file_id  | folder_id | page | field |value 
  ----------------------------------------------------------------------------------------------------
  
   21713 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | graduate                                                         |
  | 21714 |          1 |       1 |    NULL |      NULL |     563 | acadsearch| rad2                                                                  |
  | 21715 |          1 |       1 |    NULL |      NULL |     563 | override                 |                                                                       |
  | 21716 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | humanities                                                            |
  | 21717 |          1 |       1 |    NULL |      NULL |     563 | acadsearch                 |                                                                       |
  | 21718 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | rad1                                                                 |
  | 21719 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | gwcugcampus                                                           |
  | 21720 |          1 |       1 |    NULL |      NULL |     563 | breadcrumb               |                                                                       |
  | 21721 |          1 |       1 |    NULL |      NULL |     563 | acadsearch                 |                                                                       |
  | 21722 |          1 |       1 |    NULL |      NULL |     563 | acadsearch   | Psychology (BA)                                                       |
  | 21723 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | Psychology                                                            |
  | 21724 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | rad1                                                                  |
  | 21725 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | sciences                                                              |
  | 21726 |          1 |       1 |    NULL |      NULL |     563 | acadsearch | gwcugcampus   
  </code></pre>