Single result not showing when joining table

I have modified a SQL query in WordPress through a filter for the geomywp plugin so that I can search users by taxonomy and distance.

The end query looks like this

SELECT SQL_CALC_FOUND_ROWS u.ID as id   FROM  wp_users u 
INNER JOIN wppl_friends_locator gmwlocations ON u.ID = gmwlocations.member_id 
INNER JOIN wp_term_relationships ON u.ID = wp_term_relationships.object_id
WHERE wp_term_relationships.term_taxonomy_id = '47'

The term_taxonomy_id changes depending on what category they are searching for.

The really weird thing about this is that it only works if there is more than 1 result. If there is just 1 result then this query shows 0 results.

If I remove the join to the geolocation table like so:

SELECT SQL_CALC_FOUND_ROWS u.ID as id   FROM  wp_users u 
INNER JOIN wp_term_relationships ON u.ID = wp_term_relationships.object_id
WHERE wp_term_relationships.term_taxonomy_id = '47'

Then it works for taxonomies that only have a single user.

I can’t figure out why joining this table stops the query working for single results. I have never come across this before. I ran the query outside of wordpress direct in mysql and same behaviour occurs.

I need to leave this join for when plugin searches by distance. Can anyone shed light on this odd behaviour?

Answer

You can try joining the tables together using a LEFT JOIN, which would prevent records on the left side of the join from being filtered off in the event that no match occurs:

SELECT SQL_CALC_FOUND_ROWS u.ID as id
FROM wp_users u 
LEFT JOIN wppl_friends_locator gmwlocations
    ON u.ID = gmwlocations.member_id 
LEFT JOIN wp_term_relationships
    ON u.ID = wp_term_relationships.object_id
WHERE wp_term_relationships.term_taxonomy_id = '47'