INNER JOIN And Row_Num() Function Problems

Question

I have a stored procedure which does sorting pnd Paging like the Sorting Custom Paged Results of by Scott Michell.

I have two tables: Article and Category. My stored procedure works fine for Article table, but I want add a column from the Category table into the query (Inner Join I mean ).

Actually I can't do it like Scott Michell has done, because there are some columns that are similar in both tables (when I do like Scott all the time I get "Ambiguous Column Error").

My stored procedure without inner join is :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Articles_SelectByCategoryId]
    @CategoryId int,
    @startRowIndex int = -1,
    @maximumRows int = -1,
    @sortExpression nvarchar(50),
    @recordCount int = NULL OUTPUT
AS
    IF (@recordCount IS NOT NULL)
    BEGIN
        SET @recordCount = (SELECT COUNT(*) 
                            FROM [dbo].[Articles] 
                            WHERE [CategoryId] = @CategoryId)
        RETURN
    END

    IF LEN(@sortExpression) = 0
       SET @sortExpression = 'Id'

    DECLARE @sql nvarchar(4000)

    SET @sql = 'SELECT [Id], [AddedDate], [AddedBy], [CategoryId],
                       [Title], [Abstract], [Body]
                FROM
                    (SELECT 
                         [Id], [AddedDate], [AddedBy], [CategoryId],
                         [Title], [Abstract], [Body],
                         ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
                     FROM [dbo].[Articles]
                     WHERE CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + ') as CategoryIdInfo
         WHERE ((RowNum between (' + CONVERT(nvarchar(10), @startRowIndex) + ') AND ' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ' - 1) 
   OR ' + CONVERT(nvarchar(10), @startRowIndex) + ' = -1 OR ' + CONVERT(nvarchar(10), @maximumRows) + ' = -1)'

      -- Execute the SQL query
      EXEC sp_executesql @sql

My Category table is :

CREATE TABLE [dbo].[Category]
(
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [AddedDate] [datetime] NOT NULL,
   [AddedBy] [nvarchar](250) COLLATE Arabic_CI_AS NOT NULL,
   [Title] [nvarchar](50) COLLATE Arabic_CI_AS NOT NULL,
   [Importance] [int] NOT NULL,
   [Description] [nvarchar](300) COLLATE Arabic_CI_AS NULL,
   [ImageUrl] [nvarchar](50) COLLATE Arabic_CI_AS NULL,

   CONSTRAINT [PK_Category] 
      PRIMARY KEY CLUSTERED ([Id] ASC)
)

My Article table :

CREATE TABLE [dbo].[Articles]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AddedDate] [datetime] NOT NULL,
    [AddedBy] [nvarchar](250) COLLATE Arabic_CI_AS NOT NULL,
    [CategoryId] [int] NOT NULL,
    [Title] [nvarchar](255) COLLATE Arabic_CI_AS NOT NULL,
    [Abstract] [nvarchar](4000) COLLATE Arabic_CI_AS NULL,
    [Body] [nvarchar](max) COLLATE Arabic_CI_AS NOT NULL,
    [ReleaseDate] [datetime] NULL,
    [ExpireDate] [datetime] NULL,
    [Approved] [bit] NOT NULL,
    [Listed] [bit] NOT NULL,
    [CommentEnabled] [bit] NOT NULL,
    [OnlyForMembers] [bit] NOT NULL,
    [ViewCount] [int] NOT NULL,
    [Votes] [int] NOT NULL,
    [TotalRating] [int] NOT NULL,
 CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I don't know how to add The "Title" column of "Category" table into query.

If my Category table hadn't similar fields, surely this query works :

DECLARE @sql nvarchar(4000)
SET @sql = 
'SELECT
    [Id],
    [AddedDate],
    [AddedBy],
    [CategoryId],
    [Title],
    [Abstract],
    [Body],
    ArticleTitle
FROM
    (SELECT 

    [Id],
    [AddedDate],
    [AddedBy],
    a.[CategoryId],
    [Title],
    [Abstract],
    [Body],
    b.Title as CategoryTitle ,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum

    FROM  [dbo].[Articles] a INNER JOIN Category b on a.CategoryId = b.Id

    WHERE a.CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + '
    ) as CategoryIdInfo

If you want to Test you can download the Attachment(Tables and StoredProcedure) Thank you

Answer 1

The following code works perfectly on your provided tables - with no data in them but that shouldn't make a difference since I just want to prove the query execution, not the results.

Things to note with this code:

The sort expression must include an alias

SET @sortExpression = 'a.Id'       

All duplicated columns must be aliased

Note how a.[Id], a.[AddedDate], a.[AddedBy], a.[CategoryId], a.[Title], b.[Title] are all aliased

Column names must match between the inner and outer queries

You had a column name of ArticleTitle in your outer select, but a column of CategoryTitle in your inner select. That would never work.

DECLARE @SortExpression nvarchar(100)
SET @sortExpression = 'a.Id'        

DECLARE @sql nvarchar(4000) 
SET @sql =  
'SELECT 
    [Id], 
    [AddedDate], 
    [AddedBy], 
    [CategoryId], 
    [Title], 
    [Abstract], 
    [Body], 
    CategoryTitle 
FROM 
    (SELECT  

    a.[Id], 
    a.[AddedDate], 
    a.[AddedBy], 
    a.[CategoryId], 
    a.[Title], 
    [Abstract], 
    [Body], 
    b.Title as CategoryTitle , 
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum 

    FROM  [dbo].[Articles] a INNER JOIN Category b on a.CategoryId = b.Id 

    WHERE a.CategoryId = ' + CONVERT(nvarchar(10), @CategoryId) + ' 
    ) as CategoryIdInfo' 

      -- Execute the SQL query 
  EXEC sp_executesql @sql