CODEWITHSUNDEEP
sqlsql-servert-sql
Farzaneh Talebi
Farzaneh Talebi

Reputation: 915

How to use SQL ROW_NUMBER with INNER JOIN?

I have written this query to get data for special keyword:

ALTER procedure [dbo].[GetAllSpecialPaperTags]
    @PKeyword nvarchar(200)
as
begin
   select 
      Papers.PID, Papers.PTitle, Papers.PaperSummary 
  from 
      PaperKeywords 
  left join 
      PaperTags on PaperKeywords.PKeyID = PaperTags.PKeyID
  left join 
      Papers on PaperTags.PID = Papers.PID 
  where 
      PaperKeywords.PKeyword = @PKeyword
end

I want use this article for custom paging : Custom Paging using SQL Server Stored Procedure

I wrote this query but I'm getting an error:

create procedure [dbo].[GetAllSpecialPaperTags]
   @PageIndex INT = 1
   ,@PageSize INT = 10
   ,@RecordCount INT OUTPUT
   ,@PKeyword nvarchar(200)
as
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [Papers.PID] ASC
      )AS RowNumber
    ,Papers.PID , Papers.PTitle , Papers.PaperSummary
     INTO #Results
      from PaperKeywords 
        left join PaperTags on PaperKeywords.PKeyID = PaperTags.PKeyID
        left join Papers on PaperTags.PID = Papers.PID where PaperKeywords.PKeyword = @PKeyword

      SELECT @RecordCount = COUNT(*)
      FROM #Results

      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

      DROP TABLE #Results
end

Error:

Msg 207, Level 16, State 1, Procedure GetAllSpecialPaperTags, Line 11
Invalid column name 'Papers.PID'.

Why?

Upvotes: 0

Views: 696

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is your order by expression:

        ORDER BY [Papers.PID] ASC

It is looking for a column named in its entirety "Papers.PID". It is not looking for the PID column in Papers. Just drop the braces:

        ORDER BY Papers.PID ASC

Upvotes: 3

Related Questions