Map groups to parent products using nested subqueries
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.