CODEWITHSUNDEEP
mysqlsql
akronymn
akronymn

Reputation: 2446

MYSQL group results by one column but separate for unique second column

I know there are similar questions out there but I can't find this particular case among them. If someone knows where this is answered please hook me up with a link. Otherwise here goes.

I have a table which has two fields I'm interested in - code, and id. None of these are unique though there is a company field which combines with the id field to make the primary key.

I need a list of all codes and names which have more than one name associated with the same code. so if my date looks like this:

| code          | name          | company                                            |
+---------------+---------------+----------------------------------------------------+
| 00009         | name          | 1                                                  |
| 00009         | name          | 2                                                  |
| 00009         | name          | 3                                                  |
| 00009         | name          | 4                                                  |
| 00009         | diff name     | 1                                                  |
| 00014         | Foo           | 2                                                  |
| 00014         | foo           | 3                                                  |
| 00014         | foo           | 4                                                  |
| 00014         | foo           | 5                                                  |
| 00014         | foo           | 6                                                  |
| 00015         | barbaz        | 1                                                  |
| 00015         | barbaz        | 2                                                  |
| 00015         | barbaz        | 3                                                  |
| 00015         | barbaz        | 4                                                  |
| 00015         | bar baz       | 5                                                  |
| 00017         | foo           | 1                                                  |
| 00018         | bar           | 1                                                  |

I need my results too look like this:

| code          | name                                                              |
+---------------+-------------------------------------------------------------------+
| 00009         | name                                                              |
| 00009         | diff name                                                         |
| 00014         | Foo                                                               |
| 00014         | foo                                                               |
| 00015         | barbaz                                                            |
| 00015         | bar baz                                                           |

I have tried several things including

SELECT DISTINCT t1.code, t1.name from items t1 
  inner join (select t2.code from items t2 group by t2.name ) t2 
  on (t1.code = t2.code)    
  group by t1.code order by t1.code;

Which of course is wrong. Thanks for any insight!

[edit] I forgot to mention one detail. I only want to list results that have more than one unique name entry for a give code. I've updated the initial data (which does not change my desired results).

[edited for typos]

Upvotes: 0

Views: 513

Answers (1)

PinnyM
PinnyM

Reputation: 35531

Use HAVING with COUNT(DISTINCT):

SELECT code, name
FROM items
WHERE code IN (
    SELECT code 
    FROM items
    GROUP BY code 
    HAVING count(DISTINCT name) > 1
) t1
ORDER BY code, name

Upvotes: 2

Related Questions