title: SQL Query: How to select multiple instances of a single item without collapsing into a group?
body: <p>I'm trying to do with following with an SQL query in Impala.  I've got a single data table that has (among other things) two columns with values that intersect multiple times.  For example, let's say we have a table with two columns for related names and phone numbers:</p>
  
  <p><code>Names        Phone Numbers
  John Smith   (123) 456-7890
  Rob Johnson  (123) 456-7890
  Greg Jackson (123) 456-7890
  Tom Green    (123) 456-7890
  Jack Mathis  (123) 456-7890
  John Smith   (234) 567-8901
  Rob Johnson  (234) 567-8901
  Joe Wolf     (234) 567-8901
  Mike Thomas  (234) 567-8901
  Jim Moore    (234) 567-8901
  John Smith   (345) 678-9012
  Rob Johnson  (345) 678-9012
  Toby Ellis   (345) 678-9012
  Sam Wharton  (345) 678-9012
  Bob Thompson (345) 678-9012
  John Smith   (456) 789-0123
  Rob Johnson  (456) 789-0123
  Kelly Howe   (456) 789-0123
  Hank Rehms   (456) 789-0123
  Jim Fellows  (456) 789-0123</code></p>
  
  <p>What I need to get from this table is a selection of each item from the Name column that has multiple entries from the Phone Numbers column associated with it, like this:</p>
  
  <p><code>Names      Phone Numbers
  John Smith  (123) 456-7890
  John Smith  (234) 567-8901
  John Smith  (345) 678-9012
  John Smith  (456) 789-0123
  Rob Johnson (123) 456-7890
  Rob Johnson (234) 567-8901
  Rob Johnson (345) 678-9012
  Rob Johnson (456) 789-0123</code></p>
  
  <p>This is the query I've got so far, but it's not quite giving me the results I'm looking for:</p>
  
  <pre><code>SELECT a.name, a.phone_number, b.phone_number, b.count1
  
  FROM databasename a
  
  INNER JOIN (
  
    SELECT phone_number, COUNT(phone_number) as count1
  
    FROM databasename
  
    GROUP BY phone_number
  
    ) b
  
  ON a.phone_number = b.phone_number;
  </code></pre>
  
  <p>Any ideas on how to improve my query to get the results I'm looking for?</p>
  
  <p>Thank you.</p>