Databases
StackOverflow2013
Postsdbo
POMYSQL group results by one column but separate for unique second column
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL group results by one column but separate for unique second column
    text
    copied!<p>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.</p> <p>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.</p> <p>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:</p> <pre><code>| 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 | </code></pre> <p>I need my results too look like this:</p> <pre><code>| code | name | +---------------+-------------------------------------------------------------------+ | 00009 | name | | 00009 | diff name | | 00014 | Foo | | 00014 | foo | | 00015 | barbaz | | 00015 | bar baz | </code></pre> <p>I have tried several things including</p> <pre><code>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; </code></pre> <p>Which of course is wrong. Thanks for any insight!</p> <p>[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).</p> <p>[edited for typos]</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload