title: Having in SQL statement isn't returning accurate results
body: <p>I have a query that returns customer data from a temp table. The temp table only stores a customer number first and last names. It has about 320 records for folks who qualified for a program. </p>
  
  <pre><code>create table #customers
  (
  customer_no int,
  fname varchar(20),
  lname varchar(55)
  )
  </code></pre>
  
  <p>What I need to do from the database is say give me everyone from our entire database who has the combination of first name and last name (to find the duplicates).  We also need to pull addresses, but we don't want to use customer number in our search because we need it for later but for now we want the comparison to be strictly on the first and last names. </p>
  
  <pre><code>select  distinct a.customer_no,
          a.fname,
          a.mname,
          a.lname,
          b.street1,
          b.street2,
          b.city,
          b.state,
          b.postal_code
  from    T_CUSTOMER a
  join    T_ADDRESS b on a.customer_no = b.customer_no
  where   (
          lname in (select lname from #customers) and 
          fname in (select fname from #customers)
          )
  order by a.lname, a.fname
  </code></pre>
  
  <p>so the query comes back with a nice long list of records - however because there is a lot of duplicates combinations (it brings back the customer number multiple times). </p>
  
  <p>for example:</p>
  
  <pre><code>customer_no fname   mname   lname   street1             city
  86224371    John    NULL    Cornid  11 Maplewood Dr.    New Haven
  86224371    John    NULL    Cornid  11 Maplewood Drive  Hartford
  86313525    John    NULL    Cornid  11 Maplewood Dr     Hartford
  86390546    John    Seth    Cornid  11 Maplewood dr.    New Haven
  </code></pre>
  
  <p>So the data is correct. 
  The only issue is I want to suppress the first line - I want to say give me unique customer numbers only. I want my output to be:</p>
  
  <pre><code>    customer_no fname   mname   lname   street1             city
      86224371    John    NULL    Cornid  11 Maplewood Dr.    New Haven
      86313525    John    NULL    Cornid  11 Maplewood Dr     Hartford
      86390546    John    Seth    Cornid  11 Maplewood dr.    New Haven
  </code></pre>
  
  <p>We have to provide basic information to clean the data -- We only want each customer number pulled once. </p>
  
  <pre><code>select  distinct a.customer_no,
          a.fname,
          a.mname,
          a.lname,
          b.street1,
          b.street2,
          b.city,
          b.state,
          b.postal_code
  from    T_CUSTOMER a
  join    T_ADDRESS b on a.customer_no = b.customer_no
  where   (
          lname in (select lname from #customers) and 
          fname in (select fname from #customers)
          )
  group by    a.customer_no,
              a.fname,
              a.mname,
              a.lname,
              b.street1,
              b.street2,
              b.city,
              b.state,
              b.postal_code
  having count(a.customer_no) &gt; 1
  order by a.lname, a.fname
  </code></pre>
  
  <p>i wrote the above to include having count customer_no > 1 but it doesn't work because it returns an empty data set. </p>
  
  <p>please advise. 
  thank you. </p>