wood burning stoves 2.0*
The moose likes JDBC and the fly likes Query to not duplicate join results (resolved) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query to not duplicate join results (resolved)" Watch "Query to not duplicate join results (resolved)" New topic
Author

Query to not duplicate join results (resolved)

Alvin Watkins
Ranch Hand

Joined: May 25, 2011
Posts: 53
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

Joined: May 25, 2011
Posts: 53
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...

 
Consider Paul's rocket mass heater.
 
subject: Query to not duplicate join results (resolved)
 
Similar Threads
tabs in JSP and passing values between tabs
SQL problem - for JDBC cowboys
org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
SQL question
Programming style for compound compositions