ERROR: column reference "b_id" is ambiguous

I know that this error means that you need to change somewhere b_id, but something I can not understand where.

Here is the query:

SELECT "b_id","b_genre","b_author","b_book_name","b_page_count","at_text","bc_name" FROM "book" LEFT JOIN "annotation" ON "b_id"="at_bid" LEFT JOIN "book_cover" ON "bc_book_id"="b_id" LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 ) as t ON t.b_id = "book".b_id

The error that falls out:

 ERROR: column reference "b_id" is ambiguous LINE 1: SELECT "b_id","b_genre","b_author","b_book_name","b_page_cou... ^

This is what I use as offset, limit, because if you use direct offset and limit, the request hangs specifically, there are a lot of records.

 LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 )

PostgreSQL 9.3.11 database

  • You have b_id in several tables. In select tablename.b_id most likely, everything will work out - splash58
  • By the way, LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 ) as t ON t.b_id = "book".b_id does not affect the query at all. because from the first book entries will be selected regardless of what is in this subquery. For left it works that way. - Mike
  • @Mike thanks. Corrected on JOIN. - Neatek

1 answer 1

If there are two tables (or a query of the same table) in which there is a "b_id" field, SQL will generate an error that literally sounds like "The b_id field b_id ambiguous", it should be understood this way: SQL does not know which of the two values ​​to display to the user.

A valid method for solving this problem is an exact indication of which of the two values ​​you want to get.

 SELECT t."b_id","b_genre","b_author","b_book_name","b_page_count","at_text","bc_name" FROM "book" LEFT JOIN "annotation" ON "b_id"="at_bid" LEFT JOIN "book_cover" ON "bc_book_id"="b_id" LEFT JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 10 LIMIT 10 ) as t ON t.b_id = "book".b_id

or

 SELECT "book"."b_id"
  • Now everything works adequately. SELECT "book"."b_id","book"."b_genre","book"."b_author","book"."b_book_name","book"."b_page_count","at_text","bc_name" FROM "book" LEFT JOIN "annotation" ON "b_id"="at_bid" LEFT JOIN "book_cover" ON "bc_book_id"="b_id" JOIN (SELECT "b_id" FROM "book" ORDER BY "b_id" DESC OFFSET 0 LIMIT 10 ) as t ON t.b_id = "book".b_id LIMIT 10 I just have all the queries formed through the php class. - Neatek
  • Using the second SELECT accessing the same table is, in principle, a violation of the logic for constructing an SQL query. Theoretically, this is a wrong move, and the use of ORDER BY "b_id" DESC OFFSET 0 LIMIT 10 should be in the original sample, then no such error will occur. Why do you have this question works many times faster than I can’t verify validly. - Ilia Nedelkov