SQL to find devices that are not subscribed to a certain service

Version 1
    This document was generated from CDN thread

    Created by: GEERT DEWINTER on 24-05-2011 09:26:22 AM
    Hello,
    I am looking for an SQL statement to find the devices that are not logged on to a certain service ex extension mobility.
    Normally all devices should have been subscribed to extension mobility and I want to find those that are not.
    Any help is appreciated.
    Best regards,
    Geert

    Subject: RE: SQL to find devices that are not subscribed to a certain service
    Replied by: GEERT DEWINTER on 27-05-2011 07:19:04 AM
    Found a solution myself.
    select device.name from device join typemodel on (device.tkmodel = typemodel.enum and typemodel.name like 'Cisco 79%') where not exists (select * from telecastersubscribedservice as tss where device.pkid = tss.fkdevice) and device.tkclass=1;
    All remarks are welcome.