CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10611

Inner join query returning all NULL result when it should return no result

This query is returning 1 record with all NULL values when there should not be a result. When there should be a result it seems to return fine.

I use inner joins to grab some account details and ratings using their table primary keys. Some of the non-primary (many to one) keys have default of NULL - maybe this causes it to join on itself somehow?

Why is it returning a row with all null values?

SELECT a.account_id, a.first_name, a.second_name, a.points, c.body, c.creation_time, AVG(t.rating_overall)
FROM comments AS c
INNER JOIN accounts AS a
ON c.account_id=a.account_id
INNER JOIN ratings AS t
ON t.blogger_id=a.account_id
WHERE c.blog_id = ?
ORDER BY c.creation_time ASC"

Upvotes: 3

Views: 1448

Answers (1)

Kickstart
Kickstart

Reputation: 21533

Problem is that you are using an aggregate function (AVG). This is going to give you a single row (in the abscence of a GROUP BY clause), even with no actual matching rows.

Try this:-

SELECT a.account_id, a.first_name, a.second_name, a.points, c.body, c.creation_time, AVG(t.rating_overall)
FROM comments AS c
INNER JOIN accounts AS a
ON c.account_id=a.account_id
INNER JOIN ratings AS t
ON t.blogger_id=a.account_id
WHERE c.blog_id = ?
GROUP BY a.account_id, a.first_name, a.second_name, a.points, c.body, c.creation_time
ORDER BY c.creation_time ASC

Upvotes: 2

Related Questions