CODEWITHSUNDEEP
phpmysqlsqljoin
amburnside
amburnside

Reputation: 1903

SQL Join issue returning NULL values

I am having trouble running a query over three tables:

users, user_teams, user_messages

I wish to create a report where a line manager can see the total for incoming mail and outgoing mail for all members of their team. The query is to show zero values for a user if there are no outgoing or incoming.

My tables are as follows:

users
------
uid
forename
surname

user_teams
-------------
id
manager_id
member_id

user_messages
-------------
id
outgoing
incoming
uid
msg_date

Both manager_id and member_id relate to the uid field of users. There is a one to many relationship between managers and users.

My query so far is this:

SELECT u.uid, u.forename, u.surname,
SUM(IF(m.outgoing=0 ,1,0)) AS total_outgoing,
SUM(IF(m.incoming>0 ,1,0)) AS total_incoming,
FROM users u
INNER JOIN user_messages m
ON m.uid=u.uid AND m.msg_date>='2012-09-01' AND m.msg_date<='2013-08-31'
RIGHT JOIN user_teams ut ON ut.member_id=u.uid
WHERE ut.leader_id=?
GROUP BY u.uid
ORDER BY u.surname ASC

This functions correctly if a user has either sent or received a message. However, if the SUM for both fields is zero, I recieve NULL for uid, forename and surname.

Upvotes: 0

Views: 79

Answers (2)

Hituptony
Hituptony

Reputation: 2860

use COUNT instead of SUM or add an IFNULL clause to the select on outgoing and incoming.

Upvotes: 0

Aurimas Ličkus
Aurimas Ličkus

Reputation: 10074

Use LEFT JOIN user_messages m insted INNER JOIN user_messages m When there is no messages it's normal that you are getting (NULL for uid, forename and surname).

Upvotes: 4

Related Questions