0

I've got three tables with different records for users,

User

username |        realname   |         date
evn-az-3ju           john      11/2012 03:09:40 p.m.
jwyvm_rdyt           steve      12/2012 03:09:40 p.m.
bsMIAtWkhi           mahesh      01/2013 03:09:40 p.m.
zrObzh4um0           santa      01/2013 03:09:40 p.m.
WyVm_rDYt           grolsch      11/2012 03:09:40 p.m.

offline

username |       messageID |    message
jwyvm_rdyt           54      <message to="jwyvm_rdyt" id="t4Wa4-291" type="chat" from="evn-az-3ju"><body>test1</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
jwyvm_rdyt           78      <message to="jwyvm_rdyt" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
evn-az-3ju           89      <message to="evn-az-3ju" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
zrObzh4um0           98      <message to="zrObzh4um0" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
WyVm_rDYt           45      <message to="WyVm_rDYt" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>

roster

username |             jid       |     date
jwyvm_rdyt           evn-az-3ju      11/2012 03:09:40 p.m.
jwyvm_rdyt           zrObzh4um0      12/2012 03:09:40 p.m.
jwyvm_rdyt           bsMIAtWkhi      01/2013 03:09:40 p.m.
zrObzh4um0           WyVm_rDYt      01/2013 03:09:40 p.m.
WyVm_rDYt           zrObzh4um0      11/2012 03:09:40 p.m.

These are the tables from which i want result set like this,

 username |             realname       |     count
evn-az-3ju           john                     2
zrObzh4um0           santa                    0
bsMIAtWkhi           mahesh                   0

I want to know all the roster jid which username 'jwyvm_rdyt' has along with their realname and count of message if that message contains any roster jid in that. Like 'evn-az-3ju' is in message 54 and 78, so count 2 for that roster jid.

Any help would be greatly appreciated.

0
SELECT r.username,
       u.realname,
       ( SELECT  COUNT(*)
           FROM offline
           WHERE username = r.username
             AND message LIKE CONCAT('%', r.jid, '%')
       ) as "count"
    FROM roster AS r
    JOIN User AS u  ON r.username = u.username
    WHERE r.username = 'jwyvm_rdyt'

Sometimes it is best to do a COUNT in a subquery in the SELECT clause - to avoid over-counting when doing a JOIN. And, I think, GROUP BY is avoided.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.