I need to join these 2 tables
- Table 1: wp_usermeta, where I am storing my users’ last logged in time
- Table 2: wp_chats, where am storing chats between users
This query grabs the logged in user and counts the chats from that user to the current user. However it will not return a logged in user if there are no chats from that user.
How can I make this query return all logged in users even when there are no chats?
SELECT m.user_id, COUNT(c.from_id) as cnt
FROM wp_chats c
JOIN wp_usermeta m ON m.user_id=c.from_id
WHERE m.meta_key='user_last_login'
AND m.meta_value>=$time
AND c.to_id=$from_id
AND c.received=0
GROUP BY c.from_id;
http://sqlfiddle.com/#!2/edc6b/1
Users 2,3,4,5,6 are all logged in and should be in the output. User 7 in not logged in and does not appear.
Only 3, 6 are showing up however. How can i get all but #7 to be in the output?
EDIT perfected query in fiddle http://sqlfiddle.com/#!2/edc6b/12