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

  • 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?

  • Melissa,
     
    Thanks, this does seem to solve the problem, I just need to do a bit more testing on the  corner cases to make sure it doesn’t miss any results. Projects without Contracts and Contracts with no projects etc.
     
    I’m running this on version 15.6 and we’re busy with User Acceptance Testing for version 16.
     
    Regards,
     
    Will