Well as far as I can see, there are TWO perfectly valid ways to interpret the presence of your NULL constraint in the query you describe. Sure, one interpretation (the one you favor from your business requirements) is that you ALWAYS want results in your select where there is no task_resolution_id. Other select queries may NEVER want any row that has a null task_resolution_id.
So you have two perfectly valid ways to join your table with the given metadata constraints, and which one you want depends entirely on your application requirements and simply cannot be determined from the database metadata alone. I don't see any way you can always choose the right join clause without providing more information to OJB (via the setPathOuterJoin) on which form of the query fits your requirements. OJB by default merely selects the more efficient query (the inner join) and, probably, the most-likely-to-be-correct-in-most-situations option. You have to agree that the vast majority of joins are inner joins, right? Jay -----Original Message----- From: Jim Moore [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 7:04 PM To: 'OJB Users List' Subject: RE: Outer joins Thanks, but why? Why doesn't OJB just do it for me? Since "SELECT t.* FROM task t, task_resolution r WHERE t.task_resolution_id = r.task_resolution_id" is GUARANTEED to be wrong (since t.task_resolution_id can be null) shouldn't OJB automaticly do (in Oracle syntax) "SELECT t.* FROM task t, task_resolution r WHERE t.task_resolution_id = r.task_resolution_id (+)"? The only exeption to that is when resolution is used in the criteria. In that case an inner join can and should be used for efficiency. But otherwise it's simply wrong. Which brings up a separate, but related issue: Using the query.setPathOuterJoin("aTaskResolution") method, it does the outer join -- but too often. If your criteria has ("aTaskResolution.desc", "can_not_repro") then it still uses the outer join even though it logicly makes no sense for it to: because there's a criteria on (or through) that table it should be inner-joined. Granted, my code can do things like detect if "aTaskResolution" is used in the results but not in the criteria and issue the setPathOuterJoin if that's the case, but isn't that EXACTLY the kind of thing that OJB's supposed to do for me? Is there something I'm missing? -Jim Moore -----Original Message----- From: Guidos, Jay [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 8:31 PM To: 'OJB Users List' Subject: RE: Outer joins Hi Jim, You want to set query.setPathOuterJoin("aTaskResolution"); on the query before you execute it. That will force a outer join as you require. Jay -----Original Message----- From: Jim Moore [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 6:27 PM To: '[EMAIL PROTECTED]' Subject: Outer joins In OJB 1.0RC4 I can't figure out how to get outer joins to work. Below the rest of my email I've included a simplified version of my XML: In other words, I have a Task that has a status and may-or-may-not have a resolution (ie, nullable="true"). When I construct a ReportQuery where the return columns are ["taskName", "aTaskStatus.desc", "aTaskResolution.desc"] and a criteria of ("taskId", 123) it does an inner join with the TASK_STATUS table (as it should, since neither side can be null), but it also does an inner join with the TASK_RESOLUTION table (which it shouldn't do since taskResolutionId for the Task can be null). I've searched through the documentation, the mailing list archives, Google, everything I can think of, but can't find anything telling me how to get it to work right. I even tried forcing the issue with the setPathOuterJoin method, which obviously I shouldn't have to do since there's plenty of information for the engine to know that in the declaration, but even that's not working. I checked the docs and the DTD, but there isn't even something I can put in the reference-descriptor to tell it "This needs an outer join." Help! -Jim Moore --- <class-descriptor class="Task" table="TASK"> <field-descriptor name="taskId" column="TASK_ID" jdbc-type="BIGINT" indexed="true" nullable="false" primarykey="true"/> <field-descriptor name="taskName" column="TASK_NAME" nullable="false" jdbc-type="VARCHAR"/> <field-descriptor name="taskResolutionId" column="TASK_RESOLUTION_ID" nullable="true" jdbc-type="BIGINT"/> <field-descriptor name="caseTaskStatusId" indexed="true" nullable="false" column="CASE_TASK_STATUS_ID" jdbc-type="BIGINT"/> <reference-descriptor name="aTaskResolution" class-ref="TaskResolution"> <foreignkey field-ref="taskResolutionId"/> </reference-descriptor> <reference-descriptor name="aTaskStatus" class-ref="TaskStatus"> <foreignkey field-ref="caseTaskStatusId"/> </reference-descriptor> </class-descriptor> <class-descriptor class="TaskResolution" table="TASK_RESOLUTION"> <field-descriptor name="taskResolutionId" column="TASK_RESOLUTION_ID" jdbc-type="BIGINT" nullable="false" primarykey="true"/> <field-descriptor name="desc" column="TASK_RESOLUTION_DESC" nullable="false" jdbc-type="VARCHAR"/> <collection-descriptor name="collTask" element-class-ref="Task"> <inverse-foreignkey field-ref="taskResolutionId"/> </collection-descriptor> </class-descriptor> <class-descriptor class="TaskStatus" table="TASK_STATUS"> <field-descriptor name="taskStatusId" column="TASK_STATUS_ID" jdbc-type="BIGINT" nullable="false" primarykey="true"/> <field-descriptor name="desc" column="TASK_STATUS_DESC" nullable="false" jdbc-type="VARCHAR"/> <collection-descriptor name="collTask" element-class-ref="Task"> <inverse-foreignkey field-ref="taskStatusId"/> </collection-descriptor> </class-descriptor> --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
