I am using this query for pagination
SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY {0} {1}) AS RowNum, *
FROM Cars
WHERE IdOwner = {2}) AS Rows
WHERE RowNum > {3} AND RowNum < {4}
I want to make the pagination query on 2 tables, not on just one as i do now.
SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY {0} {1}) AS RowNum,
Cars.Id, Cars.Make, Cars.Model, Color.Name
FROM Cars
INNER JOIN Color ON Cars.ColorId = Color.Id
WHERE IdOwner = {2}) AS Rows
WHERE RowNum > {3} AND RowNum < {4}
I am getting a Incorrect syntax near the keyword 'WHERE'.
Problem solved. Thanks guys, i am such a noob :(
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.Id ) AS RowNum,
c.Id, c.Make, c.Model, d.ColorName
FROM Car AS c
INNER JOIN Color AS d
ON c.ColorId = d.Id WHERE c.IdOwner=1 ) AS Rows
WHERE RowNum > 0 AND RowNum < 11
c.Make is specified multiple times and "AS Rows" has been removed and is needed.
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.Id ) AS RowNum,
c.Id, c.Make, c.Model, d.ColorName
FROM Car AS c
INNER JOIN Color AS d
ON c.ColorId = d.Id WHERE c.IdOwner=1 ) AS Rows
WHERE RowNum > 0 AND RowNum < 11