.netsqlsql-serverado.netmssqlft

sql server join query issue


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 

Solution

  • 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