Joining queries in WordPress

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.

Read More

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?

Related posts

Leave a Reply

1 comment