views:

159

answers:

5

Say I have four tables:

------------- features --------------
id: int
name: varchar
-------------------------------------

-------- feature_categories ---------
feature_id: int
category_id: int    
-------------------------------------

----------- categories --------------
id: int
name: varchar    
-------------------------------------

------ category_subcategories -------
category_id: int
sub_category_id: int    
-------------------------------------

category has many (sub)categories, through self referential join subcategories

A feature has many categories, of those some will be subcategories, through join feature_categories

What I need is to send an array of feature ids and a (main) category id and have returned all subcategories. This is proving more difficult than I would have hoped so I'd appreciate greatly any help. Let me know if this question isn't clear.

EDIT I don't need the feature table to be included in any query. In facet, I only require the subcategory (category) name field to be returned.

A: 

does the following do the job?

SELECT *
FROM `category_subcategories` sc
  JOIN `categories` c ON sc.category_id = c.id
  JOIN `feature_categories` fc ON fc.category_id = c.id
WHERE fc.feature_id IN (0,1,2,...)
  AND c.id = main_category_id;
Tarentrulle
Hi and thanks for your answer. I'm afraid this does not return the subcategories.
mark
This looks close, however the name of the joined category is the parent category.
mark
A: 
select f.name featureName,
  c.name CategoryName,
  sc.name SubCategoryName 
from features f
join features_categories fc on f.id=fc.feature_id
join categories c on fc.category_id=c.id
join category_subcategories cs on c.id=cs.category_id
join categories sc on cs.sub_categories_id=sc.id
WHERE f.id IN (0,1,2,...)

If I understand correctly what you are asking...

Hi Joe and thanks for your answer. This works as required with the additional main_category id passed: "and c.id = @main_category_id". I'm going to go test it and see if anyone comes up with another answer. This looks fine but sql isn't my strong point.
mark
Ok actually this doesn't work with my amendment. All subcategories are returned, regardless of whether they are joined to one of the features.
mark
In this case I don't quite understand what you need. For features in (1,2,...) you need all the subcategories linked with them having a specific category. Did you checked if all the features combined have all the subcategories (I don't know if you understand this question correctly...)? If you try with only one feature? In this case does it return the correct subcategories?
Hi Joe. It correctly returns the subcategories of collections of features or of a single feature, the problem now is filtering those subcategories by a main category.
mark
Mark, the condition "and c.id = @main_category_id" doesn't do the job? I still think it is something I am missing here...
Unfortunately not, it returns all categories. Maybe this condition needs to be on a join?
mark
You can try to put it in the join, but I'm note sure it will work.
+1  A: 

Had to work through this a bit, but either below should work. 2nd one is probably more efficient:

 select f.name featureName, 
  c.name CategoryName, 
  c2.name SubCategoryName  
    FROM features f, feature_categories fc, categories c, category_subcategories sc, categories c2, feature_categories fc2
    WHERE f.id = fc.feature_id
    AND c.id = fc.category_id
    AND sc.category_id = c.id
    and c2.id = sc.sub_category_id
    and fc2.category_id = c2.id
    AND f.id in (0,1,2,...)
    and fc2.feature_id in (0,1,2,...)
    AND c.id = @main_category_id

or:

    select f.name featureName, 
  c.name CategoryName, 
  c2.name SubCategoryName  
        FROM features f 
        inner join feature_categories fc on f.id = fc.feature_id
        inner join categories c on c.id = fc.category_id
        inner join category_subcategories sc on sc.category_id = c.id
        inner join categories c2 on c2.id = sc.sub_category_id
        inner join feature_categories fc2 on fc.category_id = c2.id

        WHERE f.id in (0,1,2,...)
        AND c.id = @main_category_id

        and fc2.feature_id in (0,1,2,...)
Russ
This is one of them ugly joins.
Russ
Made small correction--should work now.
Russ
Thanks Russ! The first solution works but not the second. I removed the feature join which I don't need and referenced feature_categories.feature_id to simplify the query a little. I'm going to leave this open for the time being and see if any improvement can be made. Thanks again.
mark
A: 
select f.name featureName, 
c.name CategoryName, 
c2.name SubCategoryName  
FROM features f 
inner join feature_categories fc on f.id = fc.feature_id
inner join categories c on c.id = fc.category_id
inner join category_subcategories sc on sc.category_id = c.id
inner join categories c2 on c2.id = sc.sub_category_id
inner join feature_categories fc2 on fc.category_id = c2.id and fc2.feature_id = fc.feature_id
WHERE f.id in (0,1,2,...)
AND c.id = @main_category_id;
ceteras
A: 

This may give expected result

SELECT    cat.name
FROM       categories cat,
                 feature_categories feacat,
                 category_subcategories cat_subCat
WHERE     feacat.feature_id in (1,2,3)
AND          feacat.category_id = cat.id
AND         exists(select 1 from cat_subCat WHERE category_id = @CatID and sub_category_id=cat.id)

Anil