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.