CODEWITHSUNDEEP
mysqlsqllaravel
Saket Mayank
Saket Mayank

Reputation: 55

SQL Query not giving the desired result

I have two tables

  1. tbl_message

    id | user_id | user_name |message | society_id
1 -- 252----- Shubham----- HeyHi--------- 3
2 -- 252----- Shubham----- HeyHi--------- 3
3 -- 250----- Rahul---------- HeyHi--------- 3
4 -- 251----- Sachin--------- HeyHi--------- 3

  2. tbl_submessage

    id | post_id | user_id | submessage
1-----1----------252---------Hi-
2-----1----------252---------Hi-
3-----1----------253---------Hi-
4-----1----------253---------Hi-
5-----1----------253---------Hi-
6-----2----------254---------Hi-
7-----2----------254---------Hi-


I have to count the number of submessages in table tbl_submessage whose post_id = id of message in tbl_message.
Basically tbl_message contains post of a forum and tbl_submessage consists of conversations done within that post.

I tried the following query.

SELECT tk.*,COUNT(tp.id) FROM tbl_message  tk,tbl_submessage  tp WHERE tk.society_id=3 and  tk.id=tp.post_id;


This query is returning
id | user_id | user_name |message | society_id | COUNT(tp.id)
1 -- 252----- Shubham----- HeyHi--------- 3 -----------7


What I want is

id | user_id | user_name |message | society_id | COUNT
1 -- 252----- Shubham----- HeyHi--------- 3---------5
2 -- 252----- Shubham----- HeyHi--------- 3---------2
3 -- 250----- Rahul---------- HeyHi--------- 3---------0
4 -- 251----- Sachin--------- HeyHi--------- 3---------0

Please help me with the query.

Upvotes: 0

Views: 77

Answers (1)

Nick
Nick

Reputation: 147166

There's a couple of problems with your query: you need to change the join to a left join to get users who have no sub-messages and add a GROUP BY to give results on a by user basis. Try this:

SELECT tk.*,  COUNT(tp.id) 
FROM tbl_message tk
LEFT JOIN tbl_submessage tp
ON tp.post_id = tk.id
WHERE tk.society_id=3
GROUP BY tk.id

Upvotes: 1

Related Questions