I need help with a nested select
Basically, i have a billing system where multiple accounts can share the same contact id... (owner of the account). ie... john doe from 100 main street might have 3 accounts with us. I need to identify the accounts that share the same contact id.... which would answer the question... how many customers do we have that have multiple accounts
i can identify all the contact id's that have the multiple accounts with the following query. this does not give me the accountnumbers that i need.
Table 1) account table has the accountnumbers i need to retrieve (this has two columns of interest, Accountnumber, Accountkey(the joining variable)
Table 2) contact table has the contact information (the connector to this table from the account table is the column "AccountKey"
the following query identifies all contactkeys that are tied to multiple accounts
select b.Contactkey, count(b.contactkey) from billing.account a
inner join billing.contact b
on a.accountkey = b.accountkey
group by b.cntctkey
having count(b.cntctkey) > 1
Now i need to select all account numbers from billing.account that have a contact key in the result field from the query above. This is where i am stuck
select all accountnumbers from billing.account where accountkey is in the result of the above mentioned query.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.