Joining 3 table in MySQL

Question

I have 3 tables.

Users

id | Name
----------
1  | Richard
2  | Mark
3  | John
4  | Victor
5  | James

Airtime

id | User_id |Amount
-------------------
1  | 1       | 40
2  | 3       | 30
3  | 4       | 47



Messages

 id | User_id |Msg
-------------------
 1  | 2       | Hello
 2  | 3       | How are You
 3  | 1       | Wassup?
 4  | 3       | Hello guys

I need to display the Users list with their airtime (if they don't have display 0) alongside with the count of the messages (e.g UserID 3 has 2 messages so it should display 2).

I need to get my results as

Userid | Name    |Msg Count | Airtime

---

     1  | Richard | 1        | 40
     2  | Mark    | 1        | 0
     3  | John    | 2        | 30
     4  | Victor  | 0        | 47
     5  | James   | 0        | 0

So far this is what I have tried but it is returning results only from users who have both messages and airtime.

SELECT 
airtime.amount AS credit, 
COUNT(messages.id) AS msgcount,
users.*
FROM `users` 
LEFT JOIN airtime ON users.id = airtime.user_id 
LEFT JOIN messagelog ON users.id = messages.by
GROUP BY messagelog.by DESC

Any help will be appreciated.

Answer 1

Try this:

select u.id, u.name,
(select count(distinct m.id) from messages m where m.user_id = u.id) as 'Msg Count',
ifnull(a.amount, 0) as Airtime
from users u left join airtime a on (a.user_id = u.id);

Answer 2

As simple as:

SELECT 
SUM(COALESCE(airtime.amount,0) ) AS credit, 
COUNT(messages.id) AS msgcount,
users.*
FROM users 
LEFT JOIN airtime ON users.id = airtime.user_id 
LEFT JOIN messages ON users.id = messages.user_id
GROUP BY users.id 
ORDER BY msgcount DESC

http://sqlfiddle.com/#!2/d77a18/6

Answer 3

Well, as you appear to have figured out already you need to outer join on the users table because you want information on ALL users, regardless of whether or not they have information in the other two tables.

In many situations like this, it helps to first break down your problem into two parts. Finding the amount of air time for each user is not too difficult, as you can sum that column and group by user_id, like this:

SELECT u.id, COALESCE(SUM(a.amount), 0) AS totalAirtime
FROM users u
LEFT JOIN airtime a ON a.user_id = u.id
GROUP BY u.id;

Coalesce is a function that returns the first non-null value. So, in this case, if a user does not have any records in the airtime table, the totalAirtime column will be 0.

Next, finding the number of messages per user is a little trickier. First, you want to write a subquery that gets the count of messages for each user in that table:

SELECT user_id, COUNT(*) AS numMessages
FROM messages
GROUP BY user_id;

And outer join the users table to that (with the coalesce) function to get 0s for the users that do not exist in that table:

SELECT u.id, COALESCE(t.numMessages, 0) AS totalMessages
FROM users u
LEFT JOIN(
  SELECT user_id, COUNT(*) AS numMessages
  FROM messages
  GROUP BY user_id) t ON t.user_id = u.id
GROUP BY u.id;

Last, you can join this query with the one above using the user_id column to get all the values you need:

SELECT u.id, u.name, t1.totalMessages, t2.airtime
FROM user u
JOIN(
  SELECT u.id, COALESCE(t.numMessages, 0) AS totalMessages
  FROM users u
  LEFT JOIN(
    SELECT user_id, COUNT(*) AS numMessages
    FROM messages
    GROUP BY user_id) t ON t.user_id = u.id
  GROUP BY u.id) t1 ON t1.id = u.id
JOIN(
  SELECT u.id, COALESCE(SUM(a.amount), 0) AS totalAirtime
  FROM users u
  LEFT JOIN airtime a ON a.user_id = u.id
  GROUP BY u.id) t2 ON t2.id = t1.id;

Here is an SQL Fiddle example, which has all the intermediate steps too if it helps you.

Answer 4

You can do as

select
u.id,
u.name,
COALESCE(m.cnt,0) as `count`,
COALESCE(a.Amount,0) as  Airtime
from users u
left join Airtime a on a.User_id = u.id
left join (
  select
  User_id,
  count(*) as cnt
  from Messages
  group by User_id
)m on m.User_id = u.id

DEMO