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 :-)
tr_xref_type has all of the reciprocal associations in it.
I'm not sure I fully understood the last part of the question. Are you saying that the first query shows you the child and you want to know all of the people the child is associated to regardless of the association type?
If so, you can essentially write the same query again and replace "AND e.type in (1)" with AND a.customer_no in (your original query). In other words, use the list you have created to limit the results of the list you want.
Or, I may have that precisely backwards. :)
I think you want the associate_no.
Michael -
The other thing I'd recommend is playing with the 'Association' button in list manager and then looking at the SQL code it uses. This is often helpful for me in figuring out where things live/are pulled from in Tessi.
- Heather
Seattle Repertory Theatre
Hello, and thanks for the responses.
Levi said: "tr_xref_type has all of the reciprocal associations in it."
It does, but that's just a list of the possibilities for the "type" drop-down box. I tried playing around with it and using "Mcoutnerpart" and "Fcounterpart," to no avail.
Levi also said: "Are you saying that the first query shows you the child and you want to know all of the people the child is associated to regardless of the association type?"
No, sorry for any confusion. I want a list of children whose parents' records are associated to them with "Male" as the gender in the association (confusing, I know). So the data that would limit the search is stored on the parents' records, but I want a list of children.
Amanda said: "I think you want the associate_no."
Unfortunately I don't think that's what I want. I don't want every record associated, just ones that are associated in a certain way.
Heather said: "The other thing I'd recommend is playing with the 'Association' button in list manager and then looking at the SQL code it uses. This is often helpful for me in figuring out where things live/are pulled from in Tessi."
Actually that's exactly how I'm tinkering with SQL :-) I just wish I could have a list of the stuff I could query instead of guessing and checking. And the pre-made options in that "Associations" list only have options for one side of the association, not the other.
Thanks so much for the help! Keep it coming if you have ideas.
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