title: How to create a faceted search in mysql?
body: <p>I have the following tables with which i'm trying to create a faceted search</p>
  
  <pre><code>facets
  
  facet_id | facet_name
  --------------------
   1       | LANGUAGES
   2       | INDUSTRIES
   3       | JOB TYPES
   4       | SALARIES
   5       | LOCATIONS
  
  job_facts (Note: this is a view)
  
  job_id | facet_id | value
  ----------------------------------------
   1     | 1        | French
   1     | 2        | Sales
   1     | 3        | Permanent
   1     | 4        | 15000-20000
   1     | 5        | New York
   2     | 1        | French
   2     | 1        | Dutch
   2     | 2        | Sales
   2     | 2        | Media
   2     | 3        | Temporary
   2     | 4        | 20000-25000
   2     | 4        | 25000-30000
   2     | 5        | New York
   3     | 1        | German
   3     | 2        | Accounts
   3     | 3        | Permanent
   3     | 4        | 10000-15000
   3     | 5        | Paris
   4     | 1        | Spanish
   4     | 2        | Marketing
   4     | 3        | Permanent
   4     | 4        | 15000-20000
   4     | 5        | London
  </code></pre>
  
  <p>I can produce the intial results and the faceted navigation using the SQL below:</p>
  
  <pre><code># SQL To retrieve job data: 
  
  SELECT * FROM jobs
  
  # SQL To construct the intial HTML faceted navigation:
  
  SELECT t2.facet_name
          , t1.value
          , count(*) AS c
  FROM job_facts t1
  INNER JOIN facets t2 ON t1.facet_id = t2.facet_id
  GROUP BY t2.facet_name, t1.value
  </code></pre>
  
  <p>The html faced nav would appear as follows:</p>
  
  <pre><code>&gt; LANGUAGES
      French (2)
      German (1)
      Spanish (1)
      Dutch (1)
  
  &gt; INDUSTRIES
      Sales (2)
      Accounts (1)
      Media (1)
      Marketing (1)
  
  &gt; JOB TYPES
      permanent (3)
      temporary (1)
  
  &gt; SALARIES
      10000-15000 (1)
      15000-20000 (2)
      20000-25000 (1)
      25000-30000 (1)
  
  &gt; LOCATIONS
      New York (2)
      Paris (1)
      London (1)
  </code></pre>
  
  <p>I'm stuck on how to contruct the SQL to produce the facet navigation and the job results dynamically as the user starts selecting multiple facets. Eg if the user clicked on the facet 'French', the nav should display as follows:</p>
  
  <pre><code>&gt; LANGUAGES
      French X
  
  &gt; INDUSTRIES
      Sales (2)
      Media (1)
  
  &gt; JOB TYPES
      permanent (1)
      temporary (1) 
  
  &gt; SALARIES
      15000-20000 (1)
      20000-25000 (1)
      25000-30000 (1)
  
  &gt; LOCATIONS
      New York (2)
  
  
  #The results would list:
  
  job_id | title                           | jobtype_id | location_id | post_dt    | exp_dt
  ---------------------------------------------------------------------------------------------
   1     | French Sales Job                | 1          | 1           |   2015-04-01 | 2015-05-01
   2     | French &amp; Dutch Sales Media Job  | 2          | 1           | 2015-04-01 | 2015-05-01
  </code></pre>
  
  <p>If the user drills down further and select the facet 'temporary' the nav and results should display as follows:</p>
  
  <pre><code>&gt; LANGUAGES
     French X
  
  &gt; INDUSTRIES
     Sales (1)
     Media (1)
  
  &gt; JOB TYPES
     temporary X
  
  &gt; SALARIES
     20000-25000 (1)
     25000-30000 (1)
  
  &gt; LOCATIONS
     New York (1)
  
  #the results would list:
  
  job_id | title                           | jobtype_id | location_id | post_dt    | exp_dt
  ---------------------------------------------------------------------------------------------
   2     | French &amp; Dutch Sales Media Job  | 2          | 1           | 2015-04-01 | 2015-05-01
  </code></pre>
  
  <p>What would be the SQL to modify the jobs results and the facet navigation? Does any one know of a more elegant way to achieve this? Unfortunately I cant use Solr due to being on a hosted platform.</p>
  
  <p>Any help would be appreciated.</p>