eQL relationship report produces duplicate results

I'm trying to produce a report that shows contracts that are related to a project, but the following query produces a duplicate result:

START WITH Project
SELECT 
   Id,
   Code,
   Name,
   Projects.Left.Code,
   Projects.RelationshipType.LeftName 
WHERE 
   Code LIKE @Code 
ORDER BY 
   Code

Parents
  • Will,

    This is because eQL sees each relationship (or lack thereof) as a separate relationship.  Try adding a line like this to the WHERE clause:

    AND Projects.left.code IS NOT NULL   

    Your query would look like this:

    START WITH Project
    SELECT 
       Id,
       Code,
       Name,
       Projects.Left.Code,
       Projects.RelationshipType.LeftName 
    WHERE 
       Code LIKE @Code
    AND Projects.Left.Code IS NOT NULL   
    ORDER BY 
       Code

    As a side note, what version are you on?

Reply
  • Will,

    This is because eQL sees each relationship (or lack thereof) as a separate relationship.  Try adding a line like this to the WHERE clause:

    AND Projects.left.code IS NOT NULL   

    Your query would look like this:

    START WITH Project
    SELECT 
       Id,
       Code,
       Name,
       Projects.Left.Code,
       Projects.RelationshipType.LeftName 
    WHERE 
       Code LIKE @Code
    AND Projects.Left.Code IS NOT NULL   
    ORDER BY 
       Code

    As a side note, what version are you on?

Children
No Data