i have an issue here . i have 3 tables namely tbl_ads, tbl_ps, tbl_dealer
tbl_ads ( ad_id, ad_type, poster_id, cat, title, description, images ... , ad_status )
tbl_ps ( ps_id, name , address, city, state )
tbl_dealer ( id, name , address, city, state, pack, bussname )
now i want to search the ads based on title, description in tbl_ads but i want to search the ads based on the city and state which are in different tables depending on the tbl_ads
basically here, i want to get ads where if ad_type = 'ps' then get from tbl_ps and if ad_type = 'dealer' then get from tbl_dealer .. the relationship between the table ad and ps is tbl_ps.ps_id = poster_id and the relationship between the table ad and dealer is tbl_dealer.id = poster_id
The issue is i want to search ads based on the ad title, ad description, city and state and get a combined result of tbl_ads records only which may be ps or dealers
the city and state resides both on ps and dealer tables
i have tried all the multiple join, union and all the thing that i got from this stack overflow . i am not getting actually what i want and its really going into hell.