INNER JOIN And Row_Num() Function Problem

Question

Hi ,

I have a stored Procedure which do Sorting and Paging like the Sorting Custom Paged Results of of Scott Michell . I have two tables: Article and Category ,My StoredProcedure works fine for Article Table , But i want add a field of Category into queyr (Inner Join I mean )
Actually I can't do like the Scott Michell has done , Because there are some field that are similar at both Tables .(When i do like Scott all the time i get "Ambiguous Column Error") My StoredProcedure 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


--endregion

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
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

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 workes :

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

Have you tried something like the code below?

This uses the AS keyword to give unique names to columns in your subquery, and aliases to allow unambiguous references to columns within the subquery join itself.

Also note that ALL references to columns that are ambiguous must be aliases, this includes in your case the @sortExpression that you are dynamically injecting.

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], 
 -- CategoryTitle refers to the column name declared by that AS inside the subquery
 CategoryTitle
 [Abstract], 
 [Body], 

FROM 
 (SELECT  

 [Id], 
 [AddedDate], 
 [AddedBy], 
 -- The column aliases a and c allow us to refer to similarly names column unambiguously
 a.[CategoryId], 
 a.[Title], 
 c.[Title] as CategoryTitle,
 [Abstract], 
 [Body], 

--- This now includes an alias on the sortExpression order by
ROW_NUMBER() OVER(ORDER BY a.' + @sortExpression + ') as RowNum 

 FROM  [dbo].[Articles] a
 Join [dbo].[Category] c
 ON c.CategoryID = a.CategoryID
 WHERE c.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 

What that does is declare an alias for both the Articles and the Category table (a and c respectively) that allows you to reference their columns unambiguously.

I haven't tried this with the exact case of using Row_Number but can think of no reason why it wouldn't work.

Then using the AS keyword should give you a unique name to use with your outer select.

Answer 2

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