title: Fetch records with distinct value of one column while replacing another col's value when multiple records
body: <p>I have 2 tables that I need to join based on distinct rid while replacing the column value with having different values in multiple rows. Better explained with an example set below. </p>
  
  <pre><code>CREATE TABLE usr (rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(12) NOT NULL,
  email VARCHAR(20) NOT NULL);
  
  CREATE TABLE usr_loc
  (rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code CHAR NOT NULL PRIMARY KEY,
  loc_id INT NOT NULL PRIMARY KEY);
  
  INSERT INTO usr VALUES 
  (1,'John','john@product'),
  (2,'Linda','linda@product'),
  (3,'Greg','greg@product'),
  (4,'Kate','kate@product'),
  (5,'Johny','johny@product'),
  (6,'Mary','mary@test');
  
  INSERT INTO usr_loc VALUES 
  (1,'A',4532),
  (1,'I',4538),
  (1,'I',4545),
  (2,'I',3123),
  (3,'A',4512),
  (3,'A',4527),
  (4,'I',4567),
  (4,'A',4565),
  (5,'I',4512),
  (6,'I',4567);
  (6,'I',4569);
  </code></pre>
  
  <p>Required Result Set</p>
  
  <pre><code>+-----+-------+------+-----------------+
  | rid | name  | Code |      email      |
  +-----+-------+------+-----------------+
  |   1 | John  | B    | 'john@product'  |
  |   2 | Linda | I    | 'linda@product' |
  |   3 | Greg  | A    | 'greg@product'  |
  |   4 | Kate  | B    | 'kate@product'  |
  |   5 | Johny | I    | 'johny@product' |
  |   6 | Mary  | I    | 'mary@test'     |
  +-----+-------+------+-----------------+
  </code></pre>
  
  <p>I have tried some queries to join and some to count but lost with the one which exactly satisfies the whole scenario.</p>
  
  <p>The query I came up with is </p>
  
  <pre><code>SELECT distinct(a.rid)as rid, a.name, a.email, 'B' as code 
  FROM usr 
  JOIN usr_loc b ON a.rid=b.rid 
  WHERE a.rid IN (SELECT rid FROM usr_loc GROUP BY rid HAVING COUNT(*) &gt; 1);`
  </code></pre>