Here's a SQL query created through List Manager that I'm working with:
Select Distinct a.customer_no From t_customer a (NOLOCK) JOIN vxs_xref e (NOLOCK) ON a.customer_no = e.customer_no Where IsNull(a.inactive, 1) = 1 AND a.created_by in ('webAPI') AND a.cust_type in (9)
Every one of the records I'll be pulling has a single association to another record (we're managing some child/adult records here). Adding onto the above code, I can easily select records that have certain properties in their associations. For example:
AND e.type in (1)
But this looks only at the "side" of the association present on the record (because associations of course come in pairs, with one side created automatically when you manually create the other side).
So, how do I query that other side? If the above code pulls a list of child records, how do I query the associations to those child records?
Do I make sense? It's so hard to type this stuff out sometimes :-)
Let me explain how the association feature works in a criteria set to see if that helps.
The association feature will add associated constituents to the list or segment, but it will not apply the criteria to the associated constituents. So you could have criteria to find the parents and then add the associated children or criteria to find the children and then add the associated parents. The association type you select controls which associated constituents are added. It looks at the association type of the association on the record found by the criteria. So if your criteria finds parents, and in Associations you selected association types of Son and Daughter, it would look at the parent records to see if they have an association of Son or Daughter and then add the constituent IDs from those associations.
You also have the option to leave off whoever the criteria is finding so that you only get the associates. So you could find parents that meet your criteria, but only add associated children to the list or segment. To do this you would uncheck the Keep Base Ids box.
You can also filter the associates by a list. So you could create a list of children who meet your criteria. Then you could create a list of parents who meet your criteria, add associations for children, select the list of children you created, and uncheck the Keep Base Ids box. The final result would be children who meet your criteria on the first list that are associated with parents who meet the criteria from the second list.
I’m not understanding the criteria you want to use, so it’s hard to be more specific about exactly what you should do. What do you mean by “associated with Male?” Do you want all children who have associated fathers or all sons who have associated parents? Are there criteria not related to associations involved? Looking like your original post it seems like constituent type and constituent creation date are involved. Who should that apply to, the parents or the children?
Finally, for reference, you can read about the Associations feature of criteria sets here:
http://www.tessituranetwork.com/~/media/Documentation/Extractions%20and%20Lists/Criteria_Sets_for_Lists_and_Extractions.ashx
Kevin Sheehan
Documentation & Learning Resources Specialist
Tessitura Network
1 888 643 5778 ext 329 Office
ksheehan@tessituranetwork.com