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 :-)
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.