Fetch records with distinct value of one column while replacing another col's value when multiple records

Question

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.

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);

Required Result Set

+-----+-------+------+-----------------+
| 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'     |
+-----+-------+------+-----------------+

I have tried some queries to join and some to count but lost with the one which exactly satisfies the whole scenario.

The query I came up with is

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(*) > 1);`

`DISTINCT` is not a function, it doesn't apply to a single column. It applies to the whole `SELECT` list. — Barmar, Aug 24 '18 at 20:59
You can't put a table name in an alias. `AS b.code` should just be `AS code`. — Barmar, Aug 24 '18 at 21:04
Example result set seems not well formed or, at least, it doesn't match with example data. — Alejandro Nortes, Aug 24 '18 at 21:06
`rid` can't be a primary key in `usr_loc`, since there are duplicates. — Barmar, Aug 24 '18 at 21:10
I am using DB2. Sorry for my incorrect use of word distinct. usr_loc has primary key as combination of (rid,code,loc_id). — Kunj, Aug 24 '18 at 21:24

score 0 · Answer 1 · answered Aug 26 '18 at 01:33

You need to group by the users and count how many occurrences you have in usr_loc. If more than a single one, then replace the code by B. See below:

select
    rid,
    name,
    case when cnt > 1 then 'B' else min_code end as code,
    email
  from (
    select u.rid, u.name, u.email, min(l.code) as min_code, count(*) as cnt
      from usr u
      join usr_loc l on l.rid = u.rid
      group by u.rid, u.name, u.email
  ) x;

Seems to me that you are using MySQL, rather than IBM DB2. Is that so?

Fetch records with distinct value of one column while replacing another col's value when multiple records

1 Answers1