SQL Server can't specify the same column twice, and aliasing is not working for me

I am building a dynamic product search query using c# and dapper. I am trying to use a Common Table Expression to filter the product results, and then to order the filtered results, and implement pagination on them.

I keep getting the error:

SqlException: The column ‘ProductId’ was specified multiple times for ‘FilteredResults’.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Here is my query:

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT p.*, pv.* 
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    *, p.ProductId AS ProductIdAlias 
FROM 
    FilteredResults 
ORDER BY 
    p.ProductIdAlias 
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;

I am trying to use an alias, but it is not working. If I remove the ORDER BY p.ProductId clause in the pagination query, I then get this error:

SqlException: Incorrect syntax near ‘@queryOffset’.
Invalid usage of the option NEXT in the FETCH statement.

>Solution :

When you have a CTE like this

WITH cte AS (
   SELECT *
   FROM [Table] p
)

The table alias p within the CTE is no longer valid outside the CTE. That is, you cannot do this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT p.*
FROM cte

All you can do is this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT cte.*
FROM cte

or this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT q.*
FROM cte q

This is because p no longer exists for the outer level. This is true even if there were multiple tables used in the CTE. The original table names used inside the CTE are no longer relevant outside the CTE.

Additionally, column names returned from the CTE must be unique. If you have two tables in the CTE that both have columns with the same name, it is not enough to reference them separately by their table in the CTE’s SELECT clause.

That is, you cannot do this:

WITH cte AS (
    SELECT t1.ProductID, t2.ProductID
    FROM t1
    INNER JOIN t2 ON ...
)

And you cannot do this (if there are shared column names):

WITH cte AS (
    SELECT t1.*, t2.*
    FROM t1
    INNER JOIN t2 ON ...
)

Which means you definitely cannot do this:

WITH cte AS (
    SELECT t1.ProductID, t2.ProductID
    FROM t1
    INNER JOIN t2 ON ...
)
SELECT t1.ProductID
FROM cte

Because all three snippets cause you to have two columns with the same ProductID name.

This should be obvious once we understand the first section about table names and aliases. If neither t1 nor t2 exist outside the CTE as usable identifiers, then neither do t1.ProductID or t2.ProductID. All you have is cte.ProductID, and this is ambiguous; it could reference the column from either table, and that’s not legal.

Instead, you need to manually list out all the columns you need from both source tables, so no names are duplicated. This has always been the better option, even when no CTEs or duplicate column names are involved. Additionally, make sure to only reference the CTE itself outside of the CTE, rather than the source tables.

So it would look like this:

WITH cte AS (
    SELECT t1.ProductID As t1ProductID, t2.ProductID As t2ProductID
    FROM t1
    INNER JOIN t2 ON ...
)
SELECT cte.t1ProductID
FROM cte

For your query, it will looks like this (with the exception that you will want to add back more of the columns):

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT p.ProductID As ProductIDAlias 
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    f.ProductIdAlias 
FROM 
    FilteredResults  f
ORDER BY 
    f.ProductIdAlias 
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;