MYSQL query: USING LEFT JOIN

Question

SELECT jos_mod_gdwcomm.*, COUNT(jos_mod_gdwcomm_spam.reportid) `num_spam` 
FROM `jos_mod_gdwcomm` 
WHERE `num_spam` > 0 
LEFT JOIN `jos_mod_gdwcomm_spam` 
ON (jos_mod_gdwcomm.id = jos_mod_gdwcomm_spam.commid) 
GROUP BY jos_mod_gdwcomm.id
ORDER BY `num_spam` DESC, jos_mod_gdwcomm_spam.datetime DESC

How can i modify this query so it only returns the rows that num_spam is greater than 0.

i tryed putting

WHERE `num_spam` > 0

In a few places but no luck.

Thanks

stevevls · Accepted Answer · 2011-05-31 21:48:21Z

2

You need a HAVING clause:

SELECT jos_mod_gdwcomm.*, COUNT(jos_mod_gdwcomm_spam.reportid) `num_spam` 
FROM `jos_mod_gdwcomm` 
LEFT JOIN `jos_mod_gdwcomm_spam` 
ON (jos_mod_gdwcomm.id = jos_mod_gdwcomm_spam.commid) 
GROUP BY jos_mod_gdwcomm.id

HAVING num_spam > 0

ORDER BY `num_spam` DESC, jos_mod_gdwcomm_spam.datetime DESC

answered May 31, 2011 at 21:48

stevevls

10.9k1 gold badge50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blagovest Buyukliev · Accepted Answer · 2011-05-31 21:48:22Z

2

Try with GROUP BY jos_mod_gdwcomm.id HAVING num_spam > 0.

answered May 31, 2011 at 21:48

Blagovest Buyukliev

43.7k14 gold badges96 silver badges135 bronze badges

1 Comment

StiGMaT Over a year ago

Thanks it works ill approuve your answer in a few mins... wont let me just yet

Joe Stefanelli · Accepted Answer · 2011-05-31 21:49:02Z

1

When you want to test a condition on an aggregate function, you use a HAVING clause.

SELECT jos_mod_gdwcomm.*, COUNT(jos_mod_gdwcomm_spam.reportid) `num_spam` 
    FROM `jos_mod_gdwcomm` \
        LEFT JOIN `jos_mod_gdwcomm_spam` 
            ON (jos_mod_gdwcomm.id = jos_mod_gdwcomm_spam.commid) 
    GROUP BY jos_mod_gdwcomm.id
    HAVING COUNT(jos_mod_gdwcomm_spam.reportid) > 0 
    ORDER BY `num_spam` DESC, jos_mod_gdwcomm_spam.datetime DESC

answered May 31, 2011 at 21:49

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Collectives™ on Stack Overflow

MYSQL query: USING LEFT JOIN

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related