Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Unanswered: 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your GROUP BY has an extra column in it that isn't in the SELECT

    this isn't wrong per se but it could lead to confusing results

    the HAVING condition should most definitely be re-written as a WHERE condition, or better yet, an ON condition if the driving table is really Attribute_Enum_Value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •