Filtering out no longer subscribed organisations in database query

[HHH]

When running the query:

SELECT h_firstname, h_lastname, h_email_1, h_organization_name, h_servicename  FROM h_sys_contact
join h_sys_org_contacts on h_sys_contact.h_pk_id = h_sys_org_contacts.h_contact_id
join h_sys_organizations on h_sys_org_contacts.h_organization_id = h_sys_organizations.h_organization_id
join h_itsm_servicesubscriptions on h_sys_organizations.h_organization_id = h_itsm_servicesubscriptions.h_fk_subscriberid
join h_itsm_services on h_itsm_servicesubscriptions.h_fk_serviceid = h_itsm_services.h_pk_serviceid
where h_itsm_servicesubscriptions.h_fk_serviceid = 113 order by h_organization_name

I get organisations that are no longer subscribed to the specific service. Is there a join table that excludes these somewhere or is something else the matter?

[ArmandoDM]

Hi @HHH

when an organisation is unsubscribed from a service, the record in h_itsm_servicesubscriptions is removed so there should be no match. You can try adding the condition

'AND h_subscribertype='Customer Organization' 

in the join condition with h_itsm_servicesubscriptions . 
Maybe the query find a match, but the ID is not the one for an Organization but the one for a contact for example, which happens to be equal.

Regards

Armando



 

[HHH]

@ArmandoDM

That unfortunately didn't help. When I look in h_itsm_servicesubscriptions the connection is there but if I go to the service and look at subscribers the organisation is not listed.

I'll log this with support.

[Victor]

@HHH - there is a misunderstanding here. Joining the subscriptions table directly with the organisations table is fundamentally wrong... The relation between subscription and organisation is not done based on the organisation ID, is done based on a container ID. What you need to query/join is the containers table, not the organisations table... This is why your query is returning the information you see which misleads you thinking there is something wrong with the data or the UI... If you want to query the subscriptions table to find out what organisations are subscribed to it this is the query you would need to run:

SELECT
    h_sys_contact.h_firstname, 
    h_sys_contact.h_lastname, 
    h_sys_contact.h_email_1, 
    h_sys_organizations.h_organization_name, 
    h_itsm_services.h_servicename
FROM 
    h_itsm_servicesubscriptions 
JOIN 
    h_container ON h_container.h_id = h_itsm_servicesubscriptions.h_fk_subscriberid
JOIN 
    h_sys_organizations ON h_sys_organizations.h_organization_id = h_container.h_type_id
JOIN
    h_itsm_services ON h_itsm_services.h_pk_serviceid = h_itsm_servicesubscriptions.h_fk_serviceid 
JOIN 
    h_sys_contact ON h_sys_contact.h_organization_id = h_sys_organizations.h_organization_id
WHERE 
    h_itsm_servicesubscriptions.h_fk_serviceid = 113