29489

Using sub-query to SELECT MAX value along with LEFT JOIN

I have a query for getting search results, which works fine.

Example of successful query:

SELECT individuals.individual_id, individuals.unique_id, TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age, individuals_dynamics.id, individuals_achievements.degree FROM individuals as individuals LEFT JOIN individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id LEFT JOIN individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id WHERE $uuid_access_status $display_type $detailed_search_query ORDER BY $search_sort $search_order

From now on, I have more than one record in individuals_achievements per each individual and this is the where I would like to get the MAX value (latest id).

I tried the many different queries but always was getting an error <strong>Call to a member function rowCount() on a non-object.</strong>

I understand what that error means but I can't figure out where I'm making that mistake and what is wrong in general.

Example of my unsuccessful attempt:

SELECT individuals.individual_id, individuals.unique_id, TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age, individuals_dynamics.id, individuals_achievements.degree FROM individuals as individuals LEFT JOIN individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id INNER JOIN ( SELECT degree, MAX(id) AS latest_record FROM individuals_achievements GROUP BY latest_record ) individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id WHERE $uuid_access_status $display_type $detailed_search_query ORDER BY $search_sort $search_order

What am I missing here? Any help please?

Answer1:

This is your from clause:

FROM individuals as individuals LEFT JOIN individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id INNER JOIN ( SELECT degree, MAX(id) AS latest_record FROM individuals_achievements GROUP BY latest_record ) individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id;

I can spot at least three problems. The first is individuals_achievements AS individuals_achievements; the second is the reference to individuals_achievements.individual_id which isn't in the subquery. The third is the group by latest_record.

FROM individuals LEFT JOIN individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id LEFT JOIN individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id JOIN (SELECT ia.individual_id, MAX(ia.id) AS latest_record FROM individuals_achievements ia GROUP BY ia.individual_id ) iamax ON individuals.unique_id = iamax.individual_id and individuals_achievements.id = iamax.latest_record

This adds an additional subquery, with the id of the latest record.

By the way, it is redundant to have a table alias be the same name as the table name. That just clutters up the query. Also, it is a good idea to use table abbreviations for the aliases, such as ia for individuals_achievements. Because this answer focuses only on the from clause, I have not made that change.

Answer2:

PDO::query returns a FALSE if there's an error while processing your query, and applying rowCount to FALSE as a member function will result in the error you're seeing.

Check your query for syntax errors and parse any error messages PDO would return you.

Answer3:

You have to select individual_id in your INNER JOIN because that part you are using in your ON clause and you have selected degree, MAX(id) AS latest_record only and trying to join with individual_id which is undefined in the newly generated table by inner join

individuals_achievements AS individuals_achievements ON individuals.unique_id = individuals_achievements.individual_id

try this one

SELECT individuals.individual_id, individuals.unique_id, TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age, individuals_dynamics.id, individuals_achievements.degree FROM individuals as individuals LEFT JOIN individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id INNER JOIN ( SELECT degree, MAX(id) AS latest_record,individual_id FROM individuals_achievements GROUP BY latest_record ) individuals_achievements AS individuals_achievements_1 ON individuals.unique_id = individuals_achievements_1.individual_id WHERE $uuid_access_status $display_type $detailed_search_query ORDER BY $search_sort $search_order

Make sure you do have a column individual_id in table individuals_achievements

Answer4:

First off you're not really using the AS keyword in SQL to make your query more readable, which would help. And I'm wondering what is in the (php?) variables of the WHERE clause.

Also, your subquery isn't returning a field individual_id yet you try to JOIN on it. I think you might want this:

SELECT ind.individual_id, ind.unique_id, TIMESTAMPDIFF(YEAR,ind.day_of_birth,CURDATE()) AS age, ind_dyn.id, ind_ach.degree FROM individuals as ind LEFT JOIN individuals_dynamics AS ind_dyn ON ind.unique_id = ind_dyn.individual_id INNER JOIN individuals_achievements AS ind_ach ON ind_ach.individual_id = ind.unique_id AND ind_ach.id = ( SELECT MAX(id) FROM individuals_achievements WHERE individuals_achievements.individual_id = ind.unique_id ) WHERE $uuid_access_status $display_type $detailed_search_query ORDER BY $search_sort $search_order

Answer5:

INNER JOIN ( SELECT degree, MAX(id) AS latest_record,individual_id FROM individuals_achievements GROUP BY latest_record ) individuals_achievements AS individuals_achievements_1 ON individuals.unique_id = individuals_achievements_1.individual_id

It should be

INNER JOIN (SELECT blah) AS something ON individuals.unique_id=something.individual_id

Recommend

  • Datagridview to Clipboard with formatting
  • Increment inside angular expression
  • Why Is My Azure SQL Database Table Permanently Locked?
  • QStandardItemModel::removeRows() does not work in my use case
  • Back press fragments issue in android
  • Safari PHP form submission -file upload hangs
  • Is a .txt file created in VB different than one I'd randomly create?
  • ASP.NET, C# How to Pass a StringQuery to a custom SQL Command
  • Best practice for switching iPhone views?
  • TSQL Rolling Average of Time Groupings
  • Cypher - matching two different possible paths and return both
  • cell spacing in div table
  • MySQL performance when updating row with FK
  • XSLT foreach repeating nodes to flat
  • How to create a 2D image by rotating 1D vector of numbers around its center element?
  • How to get links to open in the native browser in iOS Meteor apps?
  • uml Composition relationships to RDF and OWL
  • Azure table store snapshot/backup capability
  • Thread 1: EXC_BAD_ACCESS (code =1 address = 0x0)
  • RxJava debounce by arbitrary value
  • D3 get axis values on zoom event
  • gspread or such: help me get cell coordinates (not value)
  • xtable package: Skipping some rows in the output
  • C: Incompatible pointer type initializing
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • The plugin 'org.apache.maven.plugins:maven-jboss-as-plugin' does not exist or no valid ver
  • Using jQuery closest() method with class selector
  • Change JButton Shape while respecting Look And Feel
  • Array.prototype.includes - not transformed with babel
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • When should I choose bucket sort over other sorting algorithms?
  • How do you troubleshoot character encoding problems?
  • How to get next/previous record number?
  • AT Commands to Send SMS not working in Windows 8.1
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Understanding cpu registers
  • How do I configure my settings file to work with unit tests?
  • Is it possible to post an object from jquery to bottle.py?