mysql - SQL search in relations -

i have main table 2 relations.

data structure , example:

a/employee id  fields 1   mike miller 2   lisa miller  b/skill aid name 1   sql 1   php  c/language aid name 1   german 

i need query shows results main table , searches keyword in relation tables.

search miller -> mike miller, lisa miller
search sql -> mike miller
search german -> mike miller

there 10.000 rows in main table , 100.000 relations. tried join query slow.

also same row main table displayed view several times when there more 1 relations row:

search miller
returns: mike miller, mike miller

(mike miller displayed more 1 time)

select fields   join b on id = b.aid join c on id = c.aid  fields '%"+$search+"%' or b.name '%"+$search+"%' or c.name '%"+$search+"%'" 

i tried fix second problem distinct rows without relations not displayed.

i want display every row main table 1 time. query need?

the problem first query, mention yourself, several duplicate rows returned. not strange, since guess relation between table , table b & c one-to-many.

in next attempt added distinct, , indeed rid off duplicates, regular join (or inner join) return matches data can joined, i.e. data exists in both joined tables.

introducing left join:

select distinct fields   left join b on id = b.aid  left join c on id = c.aid  fields '%"+$search+"%' or b.name '%"+$search+"%' or c.name '%"+$search+"%'" 

this search data table a, , table b & c joins can made. distinct make sure unique rows returned. use group same result, that's used aggregate methods.