php - How to perform inner join on a table with one to many relation? -

this search uses various webpages in database matching values of metadata. here value field has more 1 value , not sure how graduate science courses or similar. have tried many things nothing works.

select distinct page.path,page.site_id,   (select metadata_custom.value display_name    metadata_custom    field='academic search title'      , page.id=metadata_custom.page_id) value,    page.id    publish.page    inner join metadata      on page.metadata_id=metadata.id    inner join metadata_custom      on page.id=metadata_custom.page_id    field='acadsearch'      , value ('science%' , 'graduate%"); 

also, have query use value '{$term}%'

some sample tables table page:

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 | <region-render><div class="center_text"> <content/> </div></region-render>    ---------------------------------------------------------------------------------------------------- | 578 |  1 |   1 | e026b25fc0a80123019b60bb58c9f847 | db2b800fc0a80123019b60bbb5e082cd | 2887 | jeffrey-bulanda | academics/undergraduate/social-work/jeffrey-bulanda | <region-render><div class="center_text"> <content/> </div></region-render>   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    

not sure whole query supposed do, last part of query incorrect anyhow:

value ('science%' , 'graduate%') 

should be:

(value 'science%' or value 'graduate%') 

edit:

based on updated question , comments below, see no need wildcard matching or weird joins. please try following query:

select     p.`id`, p.`site_id`, p.`path`     `page` p,    `metadata_custom` mc     p.`id` = mc.`page_id`     , mc.`field` =  'acadsearch'     , (         mc.`value` =  'graduate'         or mc.`value` =  'rad1'     ) group     p.`id` 

to extend search on more metadata values add more or expressions.

the query in question contains subquery, cannot added without having single result rows every page. subquery should executed separate query, can matched on page id every page returned above query:

select     `page_id`,     `value` display_name     `metadata_custom`      `field` = 'academic search title'     , `page_id` in (<insert page ids comma separated>)