• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

SQL_CALC_FOUND_ROWS and ambiguous column names

 
Ranch Hand
Posts: 1747
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, in MySQL, I want to return the number of rows when two tables are joined like this:
Select SQL_CALC_FOUND_ROWS * from tableA join tableB on tableA.keyA = tableB.keyB.
If tableA and tableB both have columns of the same name, the MySQL server returns ambiguous column name error.
What should I do to avoid this error?
Thanks in advanced.
 
Sheriff
Posts: 6163
163
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would use the table name in front of the column name, like table_name.column_name.  So...
 
Himai Minh
Ranch Hand
Posts: 1747
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got a solution from someone:
select SQL_CALC_FOUND_ROWS * from ( select tableA.x, tableB.y from tableA join tableB on tableA.keyA = tableB.keyB).
We need SQL_CALC_FOUND_ROWS to count the number of rows.
 
Bartender
Posts: 20980
128
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not syntactically valid. Try something like:


Or, if you ONLY want to count the number of qualifying rows:


You don't need to pull detail columns if you just want an aggregate result. This query returns a simple one-row, one-column answer and that consumes a lot less resources than pulling the entire set of rows when you only want to know their number.

In fact, I believe there's an even more effective version of that:

Or something like that.
 
Himai Minh
Ranch Hand
Posts: 1747
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I tried this select count (*) as SQL_CALC_FOUND_ROWS from ..... ,
but I got this :


check the manual that corresponds to your MYSQL server version for the right syntax to use near SQL_CALC_FOUND_ROWS...."



If I use select SQL_CALC_FOUND_ROWS * from ... , it works in MySQL.

Reference:
https://cnedelcu.blogspot.com/2008/11/mysql-using-sqlcalcfoundrows-and.html
 
Tim Holloway
Bartender
Posts: 20980
128
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL_CALC_FOUND_ROWS is a reserved word. It's equivalent in my example to COUNT(*), so when you types SELECT COUNT(*) AS SQL_CALC_FOUND_ROWS it was as if you'd typed SELECT COUNT(*) AS COUNT(*) FROM ...

Which is obviously invalid syntax.

SQL is not BASIC. Upper/lower case matters. And worse, exactly HOW it matters varies in wild and strange ways between databases. That's why my example said COUNT(*) as sql_found_count_rows. Lower case.

COUNT(*) is the accepted standard. The blog entry you're using for reference uses the MYSQL-specific SQL_CALC_FOUND_ROWS construct because it wants to count all selected rows, but since the query has a LIMIT on it, the match row count and the returned row count are not the same.
 
What's gotten into you? Could it be this tiny ad?
Enterprise-grade Excel API for Java
https://products.aspose.com/cells/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!