Hi everyone,
Apologies if this is a really daft question, I'm still developing my SQL muscles.
I know that when you create a List and then try to grab the SQL code using Show Query that the code shown doesn't cover any of the selections you've made in the bottom section of the List Manager criteria window, such as "Replace with Household and add all primary affiliates".
I was wondering if someone knows the code for this? Finding all constituents in a HH AND the HH itself given a specific set of criteria is something that comes up quite often, but I've been struggling with the best way to do this, save a bunch of joins to T_AFFILATION (which usually leaves me in a tangle).
Ideas?
Thank you muchly in advance!
Meg
The code to generate the contents of the list is RP_GENERATE_LIST that contain the code Tessitura use to do those bits at the bottom.
Mark
The database provides a few views that you should look into using for this:
V_CUSTOMER_WITH_HOUSEHOLDV_CUSTOMER_WITH_PRIMARY_AFFILIATESV_CUSTOMER_WITH_PRIMARY_GROUP
Check out this presentation from TLCC for information on how to use these views: Constituent Tables and SQL Tips
Thanks Nick, that's super helpful!