• 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
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

SQL_CALC_FOUND_ROWS and ambiguous column names  RSS feed

 
Ranch Hand
Posts: 1729
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: 5797
150
Chrome Eclipse IDE Java Postgres Database VI Editor
  • 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: 1729
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: 20562
120
Android Eclipse IDE 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: 1729
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: 20562
120
Android Eclipse IDE 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.
 
WARNING! Do not activate jet boots indoors or you will see a tiny ad:
global solutions you can do in your home or backyard
https://coderanch.com/t/708587/global-solutions-home-backyard
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!