Mysql Query Incorporating Self Referential Join

[darcuss]

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.

 

The closest I have thus far is:

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 (1,2,3,4)

However this doesn't filter the subcategories by their having a main category.

 

Help would be very much appreciated as I really want to get this signed off and it is completely alluding me.

[fenway]

Why not just add another where condition to your query?

[darcuss]

Hi fenway. This additional where condition is proving to be a big problem. This has been on stack overflow now for a few days, 24 hours with a 100+ bounty, with no solution. The problem is the self referential join.

[mikosiko]

I will suggest to implement your categories - subcategories in a different and easy way

 

Categories

 

cat_id  INT,

cat_name  VARCHAR,

cat_parent INT

 

for Master Categories the field cat_parent should be 0 or NULL  for Subcategories the cat_parent field will point to the Master category (cat_id)... this should simplify your query.

 

 

[darcuss]

Hi Mikosiko and thanks for your reply. The only problem with that is a subcategory cannot belong to two categories, as is required.

 

The solution I currently have is:

    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 (@feature_ids)
        AND c.id = @main_category_id
        and fc2.feature_id in (@feature_ids)

If anyone has a better solution I'd be glad to hear it.

 

[darcuss]

Wrong solution posted:

 

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