troubleshooting Question

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

Avatar of Jim Metcalf
Jim MetcalfFlag for United States of America asked on
Microsoft SQL ServerSQL
16 Comments3 Solutions102 ViewsLast Modified:
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 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.
Join our community to see this answer!
Unlock 3 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros