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.