Event Class 80 - Missing Join Predicate

Hi all.

I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.

Issues:

1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.

2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.

Questions:

Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?

Thanks!!
bbRichbb

SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id