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]

Been trying to get this to work with a database view. Company left joined to the company service offering table yet I can't get the list to list companies without a subscription.

I can get it working within a script though by:
Look up current company sys_ids in the subscribe company table for the service offering.
Look up companies which is NOT in the above list.

[PeterWiles]

Actually, I was looking at it wrong. The database view I setup would work. I was trying to run my != query against the wrong subset of data.

[swiss_smile]

I am trying to set up the database view now.
Do you have any hint for me ?
Thanks

[PeterWiles]

Just re-reading this, do you need a list of the services with no company signed up?

If so, left join the Service offering table on to the service company offerings.
If your unsure on what a left join is, I would give it a guick Google.
Rearrange the table columns then you should be able to filter the table where the "company" field is blank.

[swiss_smile]

finally it worked out.
thank you so much for your help.
that database view makes it even easier to write a 'reference qual' for my task:

when creating a new incident, make only those services selectable, that the customer has subscribed for or NO COMPANY has subscribed for yet ...

thanks to Peter and Mark