How to display unique records in a table using MySQL

Question

I am trying to display all unique records from table.

SQL Script:

CREATE TABLE certificate(
    id INT PRIMARY KEY,
    full_name VARCHAR(200),
    address VARCHAR(200),
    date_of_birth DATETIME,
    special_code INT,
    validity VARCHAR(200)
);

INSERT INTO certificate VALUES('1','Walter White','Albuquerque 87107','1970-10-10','1000','VALID');
INSERT INTO certificate VALUES('2','Skyler White','Albuquerque 87107','1978-10-10','1001','VALID');
INSERT INTO certificate VALUES('3','Jesse Pinkman','Santa Fé 28','1979-10-10','1002','INVALID');
INSERT INTO certificate VALUES('4','Hank Schrader','El Paso 17','1980-10-10','1003','VALID');
INSERT INTO certificate VALUES('5','Hank Schrader','El Paso 17','1980-01-10','1004','VALID');
INSERT INTO certificate VALUES('6','Walter White','Albuquerque 87107','1970-10-10','1005','VALID');
INSERT INTO certificate VALUES('7','Saul Goodman','El Paso 14','1973-10-10','1006','VALID');
INSERT INTO certificate VALUES('8','Gustavo Fring','Santa Fé 190','1964-10-11','1007','INVALID');
INSERT INTO certificate VALUES('9','Walter White','Albuquerque 87107','1978-01-08','1008','VALID');
INSERT INTO certificate VALUES('10','Hank Schrader','El Paso 17','1980-10-10','1009','VALID');
INSERT INTO certificate VALUES('11','Walter White','Albuquerque 87107','1978-01-08','1010','VALID');
INSERT INTO certificate VALUES('12','Hank Schrader','El Paso 98','1980-10-10','10011','VALID');
INSERT INTO certificate VALUES('13','Hank Schrader','El Paso 98','1980-10-10','10012','VALID');

Queries:

The numbers of special code entries with valid certificates

select full_name, count(*) from certificate where validity = 'VALID' group by full_name;

All persons with valid certificates

select * from certificate where validity = 'VALID' order by full_name;

There are two people with this name but they live at a different address, so we can assume that it's not the same person and the result from first select is "incorrect"

select * from certificate where validity = 'VALID' and full_name = 'Hank Schrader';

In this selection there are three records belonging to two persons father and son.Both live at the same address but do not have the same date of birth result from first select is too "incorrect"

select * from certificate where validity = 'VALID' and full_name = 'Walter White';

So what I need:

If the name is the same, I check the address with the other entries in the table with the same name.
If the address is the same, I will check the date of birth with the other entries in the table with the same name.
If this is the case, it is the same person, if not, the person is different and only

Then can I add the record.

Ultimately, I need a list of all unique people with their ids who have a valid certificate and have more than one entry in the table (Walter White with ID 11 does not represent the same person as Walter White with ID 8)

So result will be:

full_name     | ids
----------------------
Walter White  | 1,6
Walter White  | 9,11
Hank Schrader | 4,5,10
Hank Schrader | 12,13

The full code is available on: http://sqlfiddle.com/#!9/71c251/1

How will you distinguish between Walter White and Walter White? — Strawberry
– Strawberry, Commented Jul 24, 2019 at 11:02

Fahmi · Accepted Answer · 2019-07-24 11:12:49Z

2

You can try below - use group by full_name,address,date_of_birth

DEMO

select full_name,
count(id) from certificate where validity = 'VALID'
group by full_name,address,date_of_birth having count(id)>1

edited Jul 24, 2019 at 11:12

answered Jul 24, 2019 at 11:04

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user11829790 Over a year ago

And how can I get only the persons who have more than one record?

Fahmi Over a year ago

@AdyP, you can check, I've added the logic

Strawberry · Accepted Answer · 2019-07-24 11:08:18Z

0

E.g.:

SELECT x.full_name
     , x.date_of_birth
     , GROUP_CONCAT(y.id) aliases
  FROM certificate x
  JOIN certificate y
    ON y.full_name = x.full_name
   AND y.address = x.address
   AND y.date_of_birth = x.date_of_birth
   AND y.id >= x.id
 GROUP
    BY x.full_name
     , x.date_of_birth
HAVING COUNT(aliases) > 1;

answered Jul 24, 2019 at 11:08

Strawberry

34k14 gold badges43 silver badges58 bronze badges

1 Comment

user11829790 Over a year ago

Thank you @Strawberry but the result is incorrect ... Hank Schrader | 1980-10-10T00:00:00Z | 4,10,10,12,13,13

Collectives™ on Stack Overflow

How to display unique records in a table using MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related