# Map groups to parent products using nested subqueries

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

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?

I think the formatting of your code could use some improvements :

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
)
)


Selecting every column can be expensive; I would recommend to optimize the query to select only the columns you need.

You could also potentially improve the performance (though not necessarily the execution plan) by creating a stored procedure, so the SQL engine wouldn't have to recalculate the execution plan on each call. This also make it much easier to call it from application code.

As far as improving the execution plan, you could modify the database to add indexing to SegmentCode, or add some columns to contain the individual segment codes (if that is feasible).

Short of changing the database, you could use #TempTables and add indexes on the column(s). That would increase I/O but may improve the execution plan.

On another note, you could most likely change like to in since you are comparing a single character with another. like is mostly used for string comparisons using wildcards, which are very expensive and you are not doing.

• you are right in the part of using in over like and here is screen shots as prove on concept for others ![in.png](postimg.org/image/b4e7mi3j3) ![like.png](postimg.org/image/kdgdwmcf3) – Mohamed abdelrahman Jan 24 '17 at 7:52
• another part you could use #TempTables you mean creating temp table with same structure ?? – Mohamed abdelrahman Jan 24 '17 at 7:55
• @Mohamedabdelrahman yes, if modifying the database is not an option. I've had to use this technique a few times, and while it may seem crude it has helped me improve execution significantly in some cases where the source tables didn't have the indexes I needed. – Phrancis Jan 24 '17 at 8:09
• Just make sure to copy the bare minimum of the data you need to temp tables, otherwise you will replace one problem with another. – Phrancis Jan 24 '17 at 8:22
• what if i need all columns ? – Mohamed abdelrahman Jan 24 '17 at 8:55