Hi, I'm having a bit of trouble setting up an SQL2 query for sorting that uses a join. Basically I have 2 types of Objects, Company and Contact that are joined with Company.manager = Contact.[jcr:uuid]. The type of the node is specified by the crm_type attribute. My Current query looks like this:
SELECT crm.* FROM [nt:unstructured] AS crm LEFT OUTER JOIN [nt:unstructured] AS Contact ON crm.[manager] = Contact.[jcr:uuid] WHERE ISDESCENDANTNODE(crm,'/1/') AND crm.isActive = true AND crm.crm_type = 'Organization' AND (ISDESCENDANTNODE(Contact,'/1/') AND Contact.isActive = true AND Contact.crm_type = 'Contact' ) ORDER BY Contact.name ASC where the restriction of ISDESCENDENTNODE limits to searching within user#1's path. Now this works.. but I only get rows where I have both objects, where I ideally want to get all Organizations and only order by the manager contact's name if is is defined, otherwise have that as null. What is wrong with my query? or am I doing something fundamentally wrong? The results I am getting seem to be what I would expect from an INNER JOIN... I am using jackrabbit 2.4.1. If this is a known bug or something, I can upgrade easily to 2.4.3 or 2.6... Thanks Ati
