GlideAggregate Left join

[davidwilhelm-in]

I need to get a list of companies and the number of tickets for each company. So if a customer has null records, they still need to come back in the query.

I set up my database view like below, when I click the "try it" link, the data displays as it should:

Now when I try to use this database view in a GlideAggretate query, it is excluding the customers that have no records, which I need.

var grCustNoAE = new GlideAggregate("u_customer_task_leftjoin");
grCustNoAE.addAggregate("COUNT");
grCustNoAE.addEncodedQuery("corecompany_u_invalid=false^corecompany_u_account_executiveISEMPTY^corecompany_customer=true^corecompany_u_prospect=true^tsk_numberSTARTSWITHINC^ORtsk_numberSTARTSWITHTKT^ORtsk_numberSTARTSWITHREQ");
grCustNoAE.groupBy('corecompany_sys_id');
grCustNoAE.query();
var x = 0;
while(grCustNoAE.next()){
	gs.info('CustSysID = ' + grCustNoAE.corecompany_sys_id + ' - CustName = ' + grCustNoAE.corecompany_name.toString() + ' - ' + grCustNoAE.getAggregate('COUNT'));
}

When I parse out the query that is displayed by the system, it forces the query to an inner join, even though the database view is set up as a left join.

SELECT 
	corecompany.`sys_id` AS `corecompany_sys_id`
	, count(*) AS recordcount 
FROM 
	(task tsk  
		INNER JOIN 
			core_company corecompany 
		ON 
			corecompany.`sys_id`  = tsk.`company`  
	)  
WHERE (tsk.`number` LIKE 'INC%' OR tsk.`number` LIKE 'TKT%' OR tsk.`number` LIKE 'REQ%')

Is it possible to write a left join GlideAggregate query in ServiceNow?

[Coleton]

No, but you can query against the DB view.

[davidwilhelm-in]

I'm using the Database View in my script example above.

[Coleton]

I noticed you have 'left join = true' for both tables. Wouldn't you just want to left join on the task table?

[davidwilhelm-in]

I know what is going on. I was able to get it to a left join but it is still not giving me the results I need. I need to be able to do the following kind of SQL code in GlideAggregate. Notice the 2nd select statement in the left join clause which gives me the record count.

SELECT
	comp.sys_id as CustSysID
	, ISNULL(TskCount, 0)
FROM
	core_company comp
LEFT JOIN 
	(SELECT company, count(*) as TskCount FROM task GROUP BY Company) tsk
ON
	comp.sys_id = tsk.company
WHERE
	1 = 1
	AND comp.u_invalid = 0
	AND comp.u_prospect = 1
	AND comp.customer = 1
	AND ISNULL(comp.u_account_executive, '') = ''
ORDER BY
TskCount