CODEWITHSUNDEEP
sql-serverjoinpagination
Nasko
Nasko

Reputation: 77

SQL Server pagination with JOIN - Ambiguous column name 'id'

i have trouble fixing one query which и normally use without JOIN , but now i want to JOIN couple of tables and i see one error for ambiguous column name id.

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'id'.1

The code which i use:

SELECT * FROM 
            (SELECT ROW_NUMBER() OVER(ORDER BY id desc) 
            AS RowNumber,
            d.id as iddr, d.EGN, d.Names, d.NickName, d.Residence, d.PersonalMobilePhone, d.HomeAddress,d.GarantPerson, d.InsertDate, 
            t.id, t.PossitionName, 
            de.id, de.IdDriver, de.IdPAFirms,de.IdPossition 
            FROM dbo.Driver d 
            FULL JOIN dbo.DriversEmployed de ON d.id = de.IdDriver 
            FULL JOIN dbo.CompanyPossitions t ON de.IdPossition = t.id)
        AS TEST 
        WHERE RowNumber BETWEEN ? AND ? + 1

If i try only JOIN into Management SQL studio the code JOIN succesfull. So i suppose the trouble id is here ORDER BY id clause.

Is there a way to use JOIN in this query? Thank you!

Upvotes: 1

Views: 939

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

In your ROW_NUMBER function:

ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber

You need to indicate from which table the column id is from by prefixing it with the proper table alias, i.e.

ROW_NUMBER() OVER (ORDER BY d.id DESC) AS RowNumber

Additionally, you need to assign different column alias to similar columns:

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY d.id DESC) AS RowNumber,
        d.id AS iddr, -- Here
        d.EGN,
        d.Names,
        d.NickName,
        d.Residence,
        d.PersonalMobilePhone,
        d.HomeAddress,
        d.GarantPerson,
        d.InsertDate,
        t.id AS CompanyPossitionsId, -- Here
        t.PossitionName,
        de.id AS DriversEmployedId, -- Here
        de.IdDriver,
        de.IdPAFirms,
        de.IdPossition
    FROM dbo.Driver d
    FULL JOIN dbo.DriversEmployed de
        ON d.id = de.IdDriver
    FULL JOIN dbo.CompanyPossitions t
        ON de.IdPossition = t.id
) AS TEST
WHERE
    RowNumber BETWEEN ? AND ? + 1

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

Both your Driver and CompanyPositions tables have an id column, making the name id ambiguous. You should fully qualify it in your order by clause. E.g.:

ORDER BY t.id

Upvotes: 0

Related Questions