<div>
<pre><code class="code-10 nolinks" id="code-20-42463845-1">select AccountKey, Accountnumber from billing.account
where AccountKey in
(
select a.AccountKey from billing.account a
inner join billing.contact b
on a.accountkey = b.accountkey
group by b.cntctkey
having count(b.cntctkey) &gt; 1
) MultipleContacts
order by contactkey, accountKey
</code></pre>
</div>

<div>
The inner select is not working on its own 
 
says a.accountkey is invalid in the select list because it is not contained in either an aggregate function or the group by clause
</div>

<div>
Your scenario is pretty much the reason why CTE was created. CTE is &quot;Common Table Expression&quot; and basically provide a method to do the subquery first, give it a name, so you can then do &quot;things&quot;. 
 

<pre><code class="code-10 nolinks" id="code-20-42463952-1">;with CTE_Accounts as
(
 select b.Contactkey, count(b.contactkey) c from billing.account a
 inner join billing.contact b
 on a.accountkey = b.accountkey
 group by b.cntctkey
 having count(b.cntctkey) &gt; 1
) 
 select BA.accountkey, BA.Accountnumber
 from billing.account BA
 inner join CTE_Accounts CTE on CTE.Contactkey = BA.accountkey 
 
</code></pre>
</div>

<div>
I don't believe so. &nbsp;A derived table handles that just fine -- see my code above -- and derived tables have been around for many years. 
 
CTEs were primarily created to enable recursion, including using the new &quot;hierarchyid&quot; data type. &nbsp;There were other reasons, I'm sure, but CTEs are absolutely required for recursion.
</div>

<div>
And one of the advantages of CTE is that it establishes / materialises columns (such as count(), row_number() etc). 
 
So it is also good for situations like 
<pre><code class="code-10 nolinks" id="code-20-42463981-1">;with CTE_Accounts as
(
 select b.Contactkey, count(b.contactkey) ctr from billing.account a
 inner join billing.contact b
 on a.accountkey = b.accountkey
 group by b.cntctkey
) 
 select BA.accountkey, BA.Accountnumber
 from billing.account BA
 inner join CTE_Accounts CTE on CTE.Contactkey = BA.accountkey 
 where CTE.ctr &gt; 1 
 
</code></pre>
 
And, it is called CTE as per Common Table Expression. They are SQL compliant and part of the ANSI SQL 99 specification. 
 
The fact that they can be used for recursion, is a benefit (or maybe a curse). But if that was the reason, then wouldnt MS have called it RCTE ;) 
 
Anyway can read about it here : <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql</a>
</div>

<div>
Updated query. &nbsp;You have to group by what you're selecting if not aggregating. 
 

<pre><code class="code-10 nolinks" id="code-20-42464132-1">select AccountKey, Accountnumber from billing.account
where AccountKey in
(
select a.AccountKey from billing.account a
inner join billing.contact b
on a.accountkey = b.accountkey
group by a.AccountKey
having count(b.cntctkey) &gt; 1
) MultipleContacts
order by contactkey, accountKey
</code></pre>
</div>

<div>
<blockquote>
Would normally complain about Contactkey if grouping by cntctkey. 
</blockquote>
 
I figured that the actual column name is &quot;cntctkey&quot; but for posting purposes the author used &quot;Contactkey&quot; just to make it clearer to us. &nbsp;But obviously don't know that for sure.
</div>

<div>
Apologies, I had a typo and had to fix.... &nbsp;ontactkey s/b contactkey...
</div>

<div>
The derived table is useful here to avoid joining all the rows in the contact table to the account table. &nbsp;That's just wasteful. &nbsp;We can do the grouping and count check first on the contact table, then only join what's actually needed to the account table. &nbsp;That's why I wrote the query that way, for efficiency.
</div>

<div>
Yes, a derived table can be useful. Can also be achieved by CTE. But the main point is that Jamesmetcalf74 is happy with 
 
&nbsp; &nbsp; select &nbsp;b.Contactkey, count(b.contactkey) &nbsp;from billing.account a 
&nbsp; &nbsp; inner join billing.contact b 
&nbsp; &nbsp; on a.accountkey = b.accountkey 
&nbsp; &nbsp; group by b.cntctkey 
&nbsp; &nbsp;having count(b.cntctkey) &gt;&nbsp;1 
 
as his method for finding duplicates. That was clearly stated. Maybe it is when tables are joined that the problem manifests itself. While I agree with you in principle, we simply do not know the environment well enough to make assumptions. Who knows ,there may be millions of contacts where only a subset are associated with Billing.Account. I dont think we can make those assumptions without know more. Including the assumption that the stated query runs, or, which column names are the correct ones (50:50 chance). 
 
So, from a solution perspective, the construct of 
<pre><code class="code-10 nolinks" id="code-20-42464677-1">;with CTE_Accounts as
( -- insert / replace the query below with your own working query
 select b.Contactkey, count(b.contactkey) ctr from billing.account a
 inner join billing.contact b
 on a.accountkey = b.accountkey
 group by b.cntctkey
 having count(b.cntctkey) &gt; 1
) -- Now we can use the results of the above to do &quot;things&quot;
 select BA.accountkey, BA.Accountnumber
 from billing.account BA
 inner join CTE_Accounts CTE on CTE.Contactkey = BA.accountkey 
</code></pre>
Does achieve his stated goal of using the results of his query to then find accountnumbers. Almost literally as described. But, until we hear back from jamesmetcalf74, we will be guessing.
</div>

<div>
There's no guess. &nbsp;There will be fewer rows after GROUPing than before. &nbsp;That's self-evident. 
 
I've seen many, many times in SQL Server that joining after reducing the set size performs far better than joining afterwards.
</div>

<div>
@Jamesmetcalf74, 
 
Is there anything else we can do for you ? 
 
Cheers, 
Mark
</div>

<div>
<div class="content wysiwyg-content">
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). &nbsp;ie... john doe from 100 main street might have 3 accounts with us. &nbsp;I need to identify the accounts that share the same contact id.... &nbsp; &nbsp;which would answer the question... &nbsp; 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. &nbsp;this does not give me the accountnumbers that i need. 
 
Table 1) account table has the accountnumbers i need to retrieve &nbsp;(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 &quot;AccountKey&quot; 
 
the following query identifies all contactkeys that are tied to multiple accounts 
 
select &nbsp;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) &gt;&nbsp;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. &nbsp;This is where i am stuck 
 
select all accountnumbers from billing.account where accountkey is in the result of the above mentioned query.
</div>
</div>

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.

nested select from a select statment with a &quot;having&quot; clause

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

nested select from a select statment with a &quot;having&quot; clause

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