Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
Last modified: May 22, 1996
mail to: email@example.com
Give yourself Extra Bonus Points if you know the answer without any further details. The rest of us need more information:
Two tables, parent and child, have a referential integrity relationship based on parent.parent_key and child.parent_key. Here are the rows:
parent table child table ------------ -------------------- parent_key parent_key child_key a a x a y b c c x c y dAs you can see, two parent rows b and d are childless, while the two remaining rows a and c each have two dependent rows in child.
Here's the Watcom SQL select statement that attempts to join parent and child while preserving rows in parent that don't happen to have any dependent rows in the child table:
select parent.parent_key, child.child_key from parent left outer join child where parent.parent_key = child.parent_keyUnfortunately, even though an outer join is used the result set is still missing parent rows b and d. What's the problem?
parent_key child_key a x a y c x c yThe answer lies in the where clause. It cancels the effect of the outer join by qualifying the whole select on child.parent_key and rejecting candidate rows where that column is missing.
The solution is to move the where clause to the on condition of the from clause. In this location it means "include matching child rows but if there are no child rows at all, keep this parent row anyway":
select parent.parent_key, child.child_key from parent left outer join child on parent.parent_key = child.parent_keyHere's the expected result set:
parent_key child_key a x a y b c x c y dWhen someone says "this outer join is not returning enough rows" the answer is often "check the where clause."
Other databases use different syntax for outer joins but the same problem is very common: Some part of the where clause is using an equality operator "=" instead of an outer join like "*=".
Check the where clause and look for comparisons that say "equals a column that might not exist".