title: Mysql query to fetch search results by combining multiple tables with a mapped table
body: <p>I have four tables namely companies,companies_branch,company_category_map and company_category.
  <a href="https://i.stack.imgur.com/SF3HM.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SF3HM.jpg" alt="enter image description here"></a>
  Here i can add a company head office to the companies table and if there is branches for the company in same country or different country, the branch will be added to the companies_branches table with the company head office id as the foriegn key. Since the main company and branches have multiple categories, the categories are mapped to company_category_map table with the respective ids of each master tables.</p>
  
  <p>When i just search with the companies table without the branch table joined everything works fine.</p>
  
  <p>The search is as follows.
  1. Search with only company name
  2. Search with only category
  3. Search with only country
  4. Search with any combination of the above 3</p>
  
  <p>what i need is</p>
  
  <p>I should be able to display the all the company names from the companies table if the country or category falls in either of the table and the company name should not repeat.</p>
  
  <p>Any ideas or help would be highly appreciated. I afraid i can't alter the table structure since there are thousands of records already inserted. But still if there is a need to alter the table i can give it a try without losing the data.</p>
  
  <p>what i have tried without joining the company_branches table is as follows.</p>
  
  <pre><code>&lt;?php 
  function search_companies($limit,$start,$companyname='',$category_id='',$country_id='') 
      {
          if($category)
          {
              $qry = "SELECT `companies`.* FROM `companies` INNER JOIN `company_category_map` ON (`companies`.`id` = `company_category_map`.`company_id`) INNER JOIN `company_category` ON (`company_category_map`.`category_id` = `company_category`.`id`) WHERE `companies`.`status` =1";
              $where = " and `company_category_map`.`category_id` = $category";
              if($companyname) $where .= ' and `companies`.`company_name` like "%'.$companyname.'%"';
              if($country_id) $where = " and `companies`.`country_id` = $country_id";
          }
          else
          {
              $qry = "SELECT * FROM `companies` WHERE `status` = 1";
              if($companyname) $where .= " AND `heading` LIKE '%".$companyname."%'";
              if($country_id) $where .= " AND `country_id` = $country_id";
          }
  
          $qry = $qry.$where." limit  $start, $limit";
  
          $query = $this-&gt;db-&gt;query($qry)-&gt;result();
  
          if (count($query) &gt; 0) {
              foreach ($query as $row) {
                  $data[] = $row;
              }
              return $data;
          }
          return false;
     }
  ?&gt;
  </code></pre>