Report Builder - Issue with outer joins. How to remove errant rows?

[avillanueva]

I have a report that is returning WTDocuments, Derived Images and Application Data related to that Derived Image.  So report shows all WTDocuments. If there is a derived image linked to that WTDoc, report its ID and report the application data that is link to that Derived Image.  What should return is three IDs and in cases where there is not a derived image for the WTDoc, those columns would be empty.  This is a classic SQL outer join case. What I am getting in cases where there is a derived image and application data, is a row that shows the document and the derived image but application data is null followed by another row were all three are filled in. How can I eliminate the prior case where application data is null?

Above you can see the objects and outer joins. I have derived image outer joined to document and application data outer joined to derived image.  I have tried it multiple ways and it seems to only show records where all three exist. Not what I was looking for.

Above is the object joins.  Derived Image is referenced to Document and Derived Image is liked to application data via Holder To Content.  I have some other selects since I am looking for a particular role for Derived Image and Application data but that should not be the issue.

Above is my selects where I am reporting the IDs and other data.  Here is the result below:

Notice the duplicate roles. Green is what I was looking for but I am getting these extra rows. I confirmed this is the case in TOAD. I am sure if I coded this by hand in SQL, I could eliminate this but Report Manager is somewhat limited.  Any advice to rework this query and eliminate the non-green rows?

[MikeLockwood]

I used the exact same approach and was unsuccessful using outer joins.  Still puzzled why this doesn't work.

I created a report to find all drawings that are not published (have no derived image).  Should work with outer join but doesn't.  Works by using a criteria of the EPMDoc oid NOT IN Sub-Select Derived Image (the CAD Doc ida2a2 is not in the DERIVEDIMAGE representable reference table).

Seems that there a lot of cases NOT IN as a criteria has to be used instead of outer join.

[avillanueva]

Agreed, NOT IN also might not be very efficient.  I know that QML has some different options when viewing the code than when done though the GUI. Perhaps there might be a way if I look at writing in the XML format.

[MikeLockwood]

I was very surprised at how fast NOT IN works - also something more to understand.

In any case, love being able to supply reports to people who request - relatively easy way to get very happy customers