Problems while trying to make a query using left join and group by with having over a field of the left table on MySQL

Im trying to make some queries, first, I do this one (and works):

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id 
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id;

But when I do this one, it gaves me an empty set, the difference is that it has a "HAVING" at the end of the query over a field that may be: 0, 1 or NULL (because of the LEFT JOIN, I have no groups linked to the table retailer_product)

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id 
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id HAVING G.active=1;

So, I tried the following ways but no one works:

-- HAVING G.active=1

-- HAVING G.active=1 开发者_Go百科OR G.active=NULL

-- HAVING G.active0

What is the right way to handle this on MySQL? Thanks in advance!

The proper way to compare value with NULL is IS NULL. So you need to add HAVING G.active IS NULL OR G.active=1. Your code G.active=NULL is always NULL (false):

1 = NULL // NULL - false in conditions
NULL = NULL // NULL - false in conditions
NULL IS NULL // TRUE

Why you don't add the HAVING condition on the "Group" ON condition?

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id AND G.active=1
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id;