Help with a mysql query

tl0tr
8 years ago

0

I have a table called users and another table called feedbacks

When I run this query:

SELECT users.fname,users.lname,users.username, COUNT(feedbacks.id) FROM feedbacks INNER JOIN users on users.username = feedbacks.username WHERE users.feedbacks = 1 and MONTHNAME(feedbacks.create_date) = ‘August’ and YEAR(feedbacks.create_date) = 2016 GROUP BY users.username ASC

It only gives me a list of users for whom the feedback is submitted but not for those users for whom there was no feedbacks submitted. Is there any way to get a list of all the username even if the count is zero for them ?

5replies

3voices

202views

? [bolofecal]
8 years ago

0

Trye replace INNER JOIN by LEFT JOIN or RIGHT JOIN.

?

tl0tr
8 years ago

0

@bolofecal : I tried both LEFT and RIGHT JOIN but it still gives the same data. No users with feedback = 0 for month of August :(

b1nary
8 years ago

0

well thats cos you are using WHERE users.feedbacks = 1 and i think if you remove that it should show all the users from august

tl0tr
8 years ago

0

@b1nary : I already tried it but it still shows the same result.

If I try the below query:

SELECT users.fname,users.lname,users.username, COUNT(*) FROM users INNER JOIN feedbacks on users.username = feedbacks.username where users.username = 'tl0tr' and month(feedbacks.create_date) = 8

It gives the result as :

fname = NULL  
lname = NULL  
username = NULL  
COUNT(*) = 0

I think this is the problem. Its not displaying the data over here and hence not displaying it when I run the query for all the users. Any ideas ?

tl0tr
8 years ago

0

I solved it


SELECT users.fname,users.lname,users.username, COUNT(feedbacks.id) as total FROM users LEFT JOIN feedbacks on users.username = feedbacks.username and month(feedbacks.create_date) = 8 and year(feedbacks.create_date) = 2016 WHERE users.feedbacks = 1 GROUP BY users.username ASC

Discussion thread has been locked. You can no longer add new posts.

1 of 6