Left outer filter issue

SQL++
1

Have a document that describes topics for discussion, and has a parent field to represent a hierarchy.
Want to do a left outer join, such that I can get the document, and also get it’s parent (if the field is not empty) topic title.

The topic data doc as an example (cut back for brevity):
    "kind": "productTopic",
    "kindVersion": "6.0.0",
    "productTopicId": "14",
    "parentProductTopicId": "1",
    "productTopicTitle": "Some Title"

The query that doesn't work (the ON clause causing the issue), only results wiht parentProductTopicId NOT null are being returned:
select r.*,r2.productTopicTitle as parentProductTopicTitle
from devtn r
left outer join devtn r2 on  r.parentProductTopicId  = r2.productTopicId 
where r.kind='productTopic'
and r2.kind='productTopic'
order by r.productTopicId

in other posts, it’s always the case that the lhs and rhs use the same field, but in my case it’s not, it’s the parentTopicId that needs to lookup it’s related parent productTopicId.
Pardon the ignorance on this, but can anyone give a suggestion? Been trying keys clauses and let statements but no luck. Thanks in advance.

2 
select r.*,r2.productTopicTitle as parentProductTopicTitle
from devtn r
left outer join devtn r2 on  r.parentProductTopicId  = r2.productTopicId  and r2.kind='productTopic'
where r.kind='productTopic'
order by r.productTopicId

It is outer Join, If you add inner filter on where clause it will eliminate null/missing projected (you must add that in on clause).

cc @bingjie.miao

3

Man, you’re good, thanks very much! :blush: