Left join results in empty result

Question

Am I using the correct connection method here? Trying to figure out where the problem is in the code. Try changing the inner, outer and right joins, just to try it out.

SELECT users.region, count(internal_profile_views.user_id)
from users
Left JOIN internal_profile_views
ON users.id = internal_profile_views.user_id
AND users.id = internal_profile_views.viewed_user_id
GROUP BY users.region;

Table structure: https://i.stack.imgur.com/nFUZO.jpg

The output I get:

西0 Northeast 0 South 0 Others 0 Midwest 0

Answer 1

Based on your schema, it looks like internal_profile_views represents an M:N table between user profiles and users who view that profile.

In this case, the rows in internal_profile_views with the same user_id and viewed_user_id values ​​mean that the user is viewing their own profile, and those with different values Rows indicate that the user is viewing someone else's profile.

In your join statement, you used a condition where user_id and viewed_user_id must match the same users.id record to be included in the results middle. Most likely you only intend to join on one column, not two.

Based on this, your query might be one of the following:

SELECT viewer.region as Viewer_Region, count(views.user_id) as Unique_Viewers
FROM internal_profile_views views
INNER JOIN users viewer ON views.user_id = viewer.id
GROUP BY viewer.region;

SELECT profileOwner.region as Profile_Region, count(views.user_id) as Unique_Viewers
FROM internal_profile_views views
INNER JOIN users profileOwner ON views.viewed_user_id = profileOwner.Id
INNER JOIN users viewer ON views.user_id = viewer.id
GROUP BY users.region;