Avatar of Jim Metcalf
Jim Metcalf
Flag for United States of America

asked on 

nested select from a select statment with a "having" clause

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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Jim Metcalf

8/22/2022 - Mon