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

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Using sub-query to SELECT MAX value along with LEFT JOIN