Hello! I need some help with an SQL query (MSSQL).
I need to return a set of contact IDs from a "Contact" table. However, I also have an "Account" table, which holds information about account holders. Each record from the Account table has a linked Contact record. I only want to return "Contact" records where the linked "Account" record is set to active. This part is fine, I can do this using the following query.
SELECT DISTINCT c.core_UserAccountName AS [ContactID]
FROM contact c
JOIN account a ON a.core_UserAccountName = c.core_UserAccountName
a.statecode = 0 and a.statuscode = 1 --active
The part where I'm getting stuck is this... Some Contact records are sub-user accounts. They are not directly linked to the Account table but rather through an additional table called "core_PortalAccountHolder"
. I would like to include these Contact records in my result set.
is linked to Contact.ContactId
is linked to Account.AccountId
Any help would be appreciated.