So I'm attempting to build a query that can return all active Service Offerings, with their associated Service Option Groups, Service Options, Forms, and then apparently the hard part, one specific form element from the associated form (named Category). I've seen somewhat similar posts and I've tried working backwards from the form side of things with this query:
SELECTSELECT fe.form_entity_id ,fe.form_entity_path ,ca.attr_value ,cc.last_name AS "Assignment Group"
FROM usm_form_component_attributes ca JOIN prob_ctg ctg ON ctg.sym = ca.attr_value INNER JOIN ca_contact cc ON ctg.group_id = cc.contact_uuid INNER JOIN usm_form_entities fe ON ca.form_comp_id = fe.form_entity_idWHERE ca.form_comp_id IN (SELECT [form_comp_id] FROM [mdb].[dbo].[usm_form_component_attributes] WHERE attr_value = 'Category') AND ca.attr_name = 'value'
This gives me the specific value I need from each form and a join with the Category in SDM, so I can see what Assignment Group this form will be assigned to. But I'm failing to see how I can first, figure out which form this is associated with. I'm failing to see how this relationship works, so that I can tie the form name with the associated elements and then also the service option.
In the end, I'm hoping to come up with a query that returns Service Offering Name, Service Option Group Name, Service Option Name, Form Name, Category (form element) - And then I can join this with prob_ctg and ca_contact to get the additional information I'll need. Any assistance would be greatly apreciated!