mySQL Select items from a category and its subcategories

Question

I have 2 tables for items and categories. the categories table is self joint table.

Item Table has the following columns ID, Item_Name, CategoryID

Categories Table has the following columns CATID, category_name, parent_ID

I need to to select items which are listed under a category and the subcategories of this main category but does not work. here's mysql, it returns only the sub.

Select * from 
 Items A
where 
 A.CategoryID in(select CATID from categories 
    where CATID= %value% or parent_ID=%value%)    

Answer 1

Since the fields are related, use joins. If you have some one-to-many relationships going on with the Categories table, use select distinct.

select distinct Items.*
from Items
join Categories as self_cat
    on (Items.CategoryID = self_cat.CATID)
left join Categories as parent_cat
    on (self_cat.parent_id = parent_cat.CATID)
where %value% in (self_cat.CATID, parent_cat.CATID)

Answer 2

TRY with self join of tbl_category table then inner join with tbl_item

SELECT i.ID as ItemID,
           i.item_name,
           c.catid AS categoryID,
           c.category_name AS categoryName,
           p.catid AS parentCategoryID,
           p.category_name as ParentCategoryName
 FROM tbl_item i
 INNER JOIN tbl_category p ON  p.catid = i.category_id 
 INNER JOIN tbl_category c ON  c.parent_id = p.catid 
 WHERE %value%  = p.cat_id  OR  %value%  = c.cat_id