Hi,

I'm trying to create a faceted search and I have the following schema:


Code:
locations

location_id | name
--------------------
 1   	    | New York
 2 	    | Paris
 3 	    | London


languages

language_id | name
---------------------
 1 	    | French
 2 	    | German
 3 	    | Spanish
 4 	    | Dutch

industries

industry_id | name
----------------------
 1 	    | Sales
 2 	    | Accounts
 3 	    | Media
 4 	    | Marketing


Salaries

salary_id | range
-----------------------
 1 	  | 10000-15000
 2 	  | 15000-20000
 3 	  | 20000-25000
 4 	  | 25000-30000


job_types

jobtype_id | name
---------------------
 1 	   | permanent
 2 	   | temporary 


jobs

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 & Dutch Sales Media Job  | 2          | 1           | 2015-04-01 | 2015-05-01
 3     | German Accounts Job             | 1          | 2           | 2015-04-01 | 2015-05-01
 4     | Spanish Marketing Job           | 1          | 3           | 2015-04-01 | 2015-05-01


job_languages

job_id | language_id
-------------------
 1     | 1
 2     | 1
 2     | 4
 3     | 2
 4     | 3


job_industries

job_id | industry_id
-------------------
 1     | 1
 2     | 1
 2     | 3
 3     | 2
 4     | 4


job_salaries

job_id | salary_id
------------------
 1     | 2
 2     | 3
 2     | 4
 3     | 1
 4     | 2



facets

facet_id | facet_name
--------------------
 1       | LANGUAGES
 2       | INDUSTRIES
 3       | JOB TYPES
 4       | SALARIES
 5       | LOCATIONS


CREATE VIEW job_facts AS
	SELECT  t1.job_id AS job_id
              , 1 AS facet_id
              , t3.name AS value
        FROM jobs t1
          INNER JOIN job_languages t2 ON t1.job_id = t2.job_id AND t1.exp_dt > CURDATE()
          INNER JOIN languages t3 on t2.language_id = t3.language_id
        UNION
        SELECT  t1.job_id AS job_id
              , 2 AS facet_id
              , t3.name AS value
        FROM jobs t1
          INNER JOIN job_industries t2 ON t1.job_id = t2.job_id AND t1.exp_dt > CURDATE()
          INNER JOIN industries t3 on t2.industry_id = t3.industry_id
        UNION
	SELECT  t1.job_id AS job_id
              , 3 AS facet_id
              , t2.name AS value
        FROM jobs t1
          INNER JOIN job_types t2 ON t1.jobtype_id = t2.jobtype_id AND t1.exp_dt > CURDATE()
        UNION
        SELECT  t1.job_id AS job_id
              , 4 AS facet_id
              , t3.name AS value
        FROM jobs t1
          INNER JOIN job_salaries t2 ON t1.job_id = t2.job_id AND t1.exp_dt > CURDATE()
          INNER JOIN salaries t3 on t2.salary_id = t3.salary_id
        UNION
        SELECT  t1.job_id AS job_id
              , 5 AS facet_id
              , t2.name AS value
        FROM jobs t1
          INNER JOIN locations t2 ON t1.location_id = t2.location_id AND t1.exp_dt > CURDATE()
        

job_facts

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
Whats the best way to create a faceted search in the example above. I can get the first level using the SQL below:

SQL To retrieve job data:

Code:
        SELECT * FROM jobs
SQL To construct the intial HTML faceted navigation:

Code:
        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
	ORDER BY t2.facet_name, t1.value;
Faceted Nav:

Code:
 > LANGUAGES
     French (2)
     German (1)
     Spanish (1)
     Dutch (1)

 > INDUSTRIES
     Sales (2)
     Accounts (1)
     Media (1)
     Marketing (1)

 > JOB TYPES
     permanent (3)
     temporary (1)

 > SALARIES
     10000-15000 (1)
     15000-20000 (2)
     20000-25000 (1)
     25000-30000 (1)

 > LOCATIONS
     New York (2)
     Paris (1)
     London (1)
But 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:

Code:
 > LANGUAGES
     French X
    

 > INDUSTRIES
     Sales (2)
     Media (1)
    
 > JOB TYPES
     permanent (1)
     temporary (1) 

 > SALARIES
     15000-20000 (1)
     20000-25000 (1)
     25000-30000 (1)

 > 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 & Dutch Sales Media Job  | 2          | 1           | 2015-04-01 | 2015-05-01
If the user drills down further and select the facet 'temporary' the nav and results should display as follows:

Code:
 > LANGUAGES
     French X
 
 > INDUSTRIES
     Sales (1)
     Media (1)
    
 > JOB TYPES
     temporary X

 > SALARIES
     20000-25000 (1)
     25000-30000 (1)

 > LOCATIONS
     New York (1)

the results would list:

job_id | title                           | jobtype_id | location_id | post_dt    | exp_dt
---------------------------------------------------------------------------------------------
 2     | French & Dutch Sales Media Job  | 2          | 1           | 2015-04-01 | 2015-05-01
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.

Any help would be appreciated.