Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query to not duplicate join results (resolved)

 
Alvin Watkins
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having a problem with writing a query to do what I want it to. I have three levels, or nodes, of data that I want to retrieve for users. A parent node MAY have siblings and CAN have multiple children who themselves can have siblings and can have multiple children (three levels). When the query runs, I'd like for all parents to be found and if the parent has no children I want the columns representing those children values to be blank. Instead, what I am getting is result that has my parents repeating for each child the query finds - which is fine for parents that have multiple children but this is behavior I get even if the parent has no children.

The query (while much larger) is formatted as below.



Can someone help me fix the query so that it returns no value for children that don't exist but still retrieves all parents?
 
Alvin Watkins
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Finally figured it out (always happens after you post...). I need to use LEFT OUTER JOINS instead of the standard postgres syntax for joins (which defaults to inner joins). I also modified the query a bit. For any who may be interested, the final query which returns results as I want them is this...

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic