peter

peter

  • 0
  • 170
  • 0

Paging functionality on two tables combine with UNION

Oct 11 2021 5:50 PM

I have two columns highlighted that are extra additions to make paging work. it is not correct i believe i need to unite tables first than combine the two extra parameters later to get the total rows returned and ROWID for paging functionality to operate proper.

 

SELECT TOP(@PageSize) FROM
(
SELECT 
RowID = ROW_NUMBER() OVER (ORDER BY datentime DESC),
Inbox_ProductID,
Inbox_RefItemID,

TotalRows=Count(Inboxid) OVER(),*
InboxID,
inbox_messages.ID ,
MessageType,
SenderID,
RecieverID,
[Subject],
Body,
Datentime,
Sender_Status,
Reciever_Status

 

FROM Inbox_Messages

UNION
SELECT RowID = ROW_NUMBER() OVER (ORDER BY datentime DESC),
ProductID,
RefItemID,
TotalRows=Count(id) OVER(),
id,
DISPUTE_INBOXID,
MessageType,
SenderID,
RecieverID,
[Subject],
Body,
Datentime,
Sender_Status,
Reciever_Status

FROM DISPUTE

)A WHERE A.RowID > ((@PageIndex-1)*@PageSize)


Answers (4)