sql how to do an inner join with row number

Question

I am trying to do an inner join with row number using dapper MSSQL and I keep getting this exception Additional information: The column 'id' was specified multiple times for 'threadem . I have 2 tables threads and zips and they both have a primary key of id I don't know if that has to do with it, this is my code

SELECT z.*,
       t.*
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY t.activities DESC) AS Row,
               z.*,
               t.*
        FROM   threads t
               INNER JOIN zips z
                 ON z.city = @Ucity
                    AND z.state = @Ustate
        WHERE  t.latitudes >= z.ThirtyLatMin
               AND z.ThirtyLatMax >= t.latitudes
               AND t.longitudes >= z.ThirtyLonMin
               AND z.ThirtyLonMax >= t.longitudes) AS threadem
WHERE  ROW >= 1
       AND Row <= 5

what can I fix above to stop getting this error

Give us the definitions of threads and zips so we don't have to answer in hypotheticals. — Martin Smith, Feb 22 '15 at 17:42
Possible duplicate of http://stackoverflow.com/questions/1058606/sql-2005-the-column-was-specified-multiple-times — Giorgos Betsos, Feb 22 '15 at 17:43
Your subquery should be `relational`. When you select `z.*, t.*`, you obviously get columns with same names(in your case it is ID). It is `violation of relational model`. So you should select something like `z.ID as zID, t.ID as tID, other columns...` — Giorgi Nakeuri, Feb 22 '15 at 17:51

Gordon Linoff · Accepted Answer · 2015-02-23T04:06:24.103

First, if you are using SQL Server 2012+, you can use fetch first . . . offset syntax. This is more convenient than row number.

The best way to solve your problem is to list all the columns you need explicitly. Then, if two columns from the tables have the same names, use an alias to rename them. Something like:

SELECT t.*
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY t.activities DESC) AS Row,
               z.zcol1, z.zcol2, . . .,
               t.tcol1, t.zcol2, . . 
        FROM threads t INNER JOIN
             zips z
             ON z.city = @Ucity AND z.state = @Ustate
        WHERE t.latitudes >= z.ThirtyLatMin AND
              z.ThirtyLatMax >= t.latitudes AND
              t.longitudes >= z.ThirtyLonMin AND
              z.ThirtyLonMax >= t.longitudes
      ) t
WHERE ROW >= 1 AND Row <= 5 ;

If you don't want row as a returned column in the outer query, then you need to list all the columns that you do want in the outer select.

@user1591668 . . . In most cases, it should, but you should test it. The issue is the sorting of all the rows versus sorting only for `row_number()` and joining the values back in. You can test the two versions. — Gordon Linoff, Feb 23 '15 at 04:08

sql how to do an inner join with row number

1 Answers1