Query for empty table

[swiss_smile]

Hi

I need the sys_ids from table 'service_offering' that have no entry in the related table 'service_subscribe_company' (m2m relation).

I tried the following:


// Seek active services without subscribtion
var ysys = '';
var gr1 = new GlideRecord('service_offering');
gr1.addQuery('u_active', true);
gr1.addNullQuery(service_subscribe_company); //(--> here is the Problem!)
while (gr1.next()) {
if (ysys != '') {
ysys += ',' + gr1.sys_id.toString();
} else {
ysys = gr1.sys_id.toString();
}
}

of course it doesn't work ...
what would be the right query to find service_offerings WITH NO RECORD in the related table ?

Thanks for your help.

[PeterWiles]

Weirdly, I was trying to do this on Friday.
Will be tackling it again today so will let you know if I find anything.

[Mark Stanger]

Try putting 'service_subscribe_company' in quotes and adding 'gr1.query()' below your 'addNullQuery' line. Also, make sure that 'service_subscribe_company' is really the name of the field you're trying to check for empty values in.

[swiss_smile]

hi mark

thanks for your idea.
but 'service_subscribe_company' is not a field - it is the table.

and I want to know all the services, that have no record in the related table 'service_subscribe_company'.

Hope this is understandable.

[Mark Stanger]

I see. The best way to go about that is to set up a database view with a left join. The following wiki article should get you started.

http://wiki.servicenow.com/index.php?title=Database_Views