I have the following tables
productstbl
===========================================
SegmentCode CategoryId ParentId
============================================
-----------------------------------------------------
categoriestbl
==================
CategoryId name
====================
---------------------------------------------------------
Groupstbl
=====================
GroupId name
=====================
---------------------------------------------------------
CategoriesGroupsInfo
=======================
id CategoryId GroupId
=======================
---------------------------------------------------------
the logic goes like this: I want to map groups to parent products (root node which have parentid = 0) and the subproducts of that parent will inherit theses groups by default.
The levels of products are unlimited, for example:
1 Basic foods (root node )
1.1 Baked goods
1.1.1 American breads
1.1.1.1 Adobe bread
1.2 Breads
2 Prepared foods(root node )
2.1 Appetizers
2.2 Condiments
2.3 Confectionery
Note that I know the root node of any product through first char of segmentcode.
For example, root node of 1.1.1.1 Adobe bread will have segment code 1 which is Basic foods.
Groups are only applied for root nodes and to know the subproducts have to match first number of segment code.
Here is my query it do the work but the execution plan is a headache.
select * from productstbl
where substring(SegmentCode,1,1)
like ( select substring(SegmentCode,1,1)
from categoriestbl where CatID in(
select CatID from CategoriesGroupsInfo where GroupID = 1))
Is there any alternative query?