How to combine two queries as a single result set in SQL for Categories - Subcategories?

Question

I have the following function:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE 
AS
RETURN 
(
SELECT ISBN FROM (
SELECT ISBN, 
ROW_NUMBER() OVER(AddedDate DESC) AS RowNum
FROM (
        SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblSubCategories
        WHERE SubCategoryID = @SubCategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern 
) AS Info
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)

Which thanks to help from others on StackOverflow, would list all the items with a Given SubCategory - however I want to be able to include the following:

    SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
    FROM tblCategories
    WHERE CategoryID = @CategoryID) Cats
    JOIN tblStock Stock
    ON Stock.CategoryCode LIKE Cats.Pattern

So that it gets the Categories as a Set, then the SubCategories is a SubSet of this, for example I have a Category of EG, which contains two SubCategories EG-EG and EG-IE which themselves are a List of category codes, for example:

EG-EG
- ETC
- ECT
- TCE
EG-IE
- EIEG
- EGIE

How to I get it so it does the Categories then from this List then does thr SubCategories, as part of this I need a "NOT" behaviour as there will be a General Category which will pick up all the left over SubCategories, that are not specifically stated, but would be picked up by the Category Query.

I just cannot find the right combination for this - SubCategories and Categories work seperately but I want them to be SuperSets and SubSets of each other.

---

Here is the the ListStockByCategoryFunction:

CREATE FUNCTION [dbo].[ListStockByCategory]
(   
    @CategoryID varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE AS
RETURN
(
SELECT ISBN FROM (SELECT ISBN, 
ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
FROM (
        SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblCategory
        WHERE CategoryID = @CategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern
) AS Info
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)

---

I have a Solution which works, however the performance is not acceptable, could anyone help with this as I have been working on it for some time and cannot seem to find a way to optimise this - the SubCategories are a SubSet of the Categories if this helps, see the example below:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID     varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE 
AS
RETURN (
SELECT ISBN FROM (
SELECT ISBN,
ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
FROM BooksInStock WHERE ISBN IN
(SELECT ISBN FROM(SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblCategories
        WHERE CategoryID = @CategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern
WHERE 
ISBN IN
(SELECT ISBN FROM(SELECT DISTINCT RTRIM(LTRIM(CategoryCode)) + '%' AS Pattern
        FROM tblSubCategories
        WHERE SubCategoryID = @SubCategoryID) Cats
        JOIN tblStock Stock
        ON Stock.CategoryCode LIKE Cats.Pattern))
) AS Info WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1)

Answer 1

Utlimately, if I'm following the requirements right (and without table schemas and knowledge of the data contained, it's pretty convoluted), the inner subquery that you have aliased as "Info" will need to contain two UNION ALLed queries--the "new" one on tblCategories and the "old" one on tblSubCategories.

The tricky part will be working out ordering, particularly since your current sorting criteria (AddedDate) appears to have nothing to do with your desired sorting criteria (Category and SubCategory). Work that out, and based on that perhaps add some columns that will be used only for ordering.

Answer 2

Try this for the core SELECT:

SELECT ISBN FROM tblStock a
WHERE EXISTS (
    SELECT * FROM tblCategories b
    WHERE b.CategoryID = @CategoryID
      AND a.CategoryCode LIKE RTRIM(LTRIM(b.CategoryCode))+'%'
    )
  AND EXISTS (
    SELECT * FROM tblSubCategories b
    WHERE b.SubCategoryID = @SubCategoryID
      AND a.CategoryCode LIKE RTRIM(LTRIM(b.CategoryCode))+'%'
    )

That gets rid of two DISTINCTs.

Beyond that, you still have an expensive-looking query that would probably benefit from better indexing.

If you provided the DDL statements for tblStock, tblCategories, tblSubCategories, and BooksInStock (including all indexes and key constraints), I could provide some recommendations there.

Answer 3

Well, its impossible to say without the table definitions, keys, indexes query plans or data examples, but I think that this will help some:

CREATE FUNCTION [dbo].[ListStockBySubCategory]
(   
    @CategoryID     varchar(10),
    @SubCategoryID  varchar(10),
    @startRowIndex  int,
    @maximumRows    int
)
RETURNS TABLE AS
RETURN 
(
    SELECT ISBN FROM 
    (
        SELECT  ISBN,  ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS RowNum
        FROM BooksInStock 
        WHERE EXISTS
        (        
            SELECT *

            FROM tblStock AS stk
            JOIN tblStock AS stk2         ON stk.ISBN = stk2.ISBN
            JOIN tblCategories AS cat     ON cat.CategoryID = @CategoryId
            JOIN tblSubCategories AS sub  ON cat.CategoryID = sub.CategoryID

            WHERE cat.CategoryID = @CategoryId
              AND sub.CategoryID = @CategoryId
              AND bis.ISBN = stk.ISBN
              AND  stk.CategoryCode LIKE RTRIM(LTRIM(cat.CategoryCode))+'%'
              AND stk2.CategoryCode LIKE RTRIM(LTRIM(sub.CategoryCode))+'%'
        )
    ) AS Info 
    WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
)