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