Q:

SQL query to get filters with expected values for a web view

I've got a web-application that requires data from a db. It is a simple list of jobs where each job has certain tags and also a location. Now I want to get a list that gives me all possible filter options... like this:
FILTER-ATTRIBUTE    FILTER-CATEGORY    MATCHING-JOBS
programming         tag                9
car-washing         tag                3
cooking             tag                13
Munich              city               16
San Diego           city               9

What I do have is a query with UNIONS, but this is first of all not really fast and it's ugly to filter...because I need several WHERE clauses there if the user applies a filter (e.g. if San Diego is selected, I will restrict the query to return only tags with relevance to San Diego. In this example, programming only).
SELECT
  jt.tag_name as name,
  COUNT(jt.job_id) as available_jobs,
  'tag' as filter_cat
FROM job_tag_new jt WHERE jt.job_id IN (20002,30002) GROUP BY(jt.tag_name)
UNION
SELECT
  j.company as name,
  COUNT(j.id) as available_jobs,
  'company' as filter_cat
FROM job j WHERE j.id IN (20002,30002) GROUP BY(j.company)
UNION
SELECT
  l.city as name,
  COUNT(j.id) as available_jobs,
  'city' as filter_cat
FROM job j left join location l on l.id = j.location WHERE j.id IN (20002,30002) GROUP BY(l.city)

Last but not least... there is not only one table because several tables/objects can be used for filtering ;-)
Any suggestions would be nice. Even if I should take a complete different approach!
Regards,
Martin

A:

After some more research, I came across an interesting approach and I would like to share this with with you:
SELECT
    name,
    COUNT(job_id)
FROM   (
    SELECT j.id AS job_id, loc.city, j.company, jt.tag_name
    FROM job_tag_new jt
    LEFT JOIN job j ON jt.job_id = j.id
    LEFT JOIN location loc ON loc.id = j.location
) base
CROSS APPLY (
VALUES
    ([city]),
    ([company]),
    ([tag_name])
) 
V(name)
GROUP BY name

The only thing which is missing here is to get a separate column that tells me the column name before the cross apply. Maybe someone has an idea for this ;-)

