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