Python241820 Python241820 - 6 months ago 19
SQL Question

How to select a specific person in MySQL?

I created the following tables:

create table people
(
ID varchar(10),
name varchar(35),

CONSTRAINT pk_ID PRIMARY KEY (ID)

);

create table numbers
(
code varchar(10),
ID varchar(10),
number numeric,

CONSTRAINT pk_code PRIMARY KEY (code)

);


I inserted the following datas:

insert into people(ID, name)
values('fdx1','Peter');
insert into people(ID, name)
values('fdx2','Alice');
insert into people(ID, name)
values('fdx3','Louis');

insert into numbers(code, ID, number)
values('001','fdx1',1);
insert into numbers(code, ID, number)
values('002','fdx1',1);
insert into numbers(code, ID, number)
values('003','fdx2',2);
insert into numbers(code, ID, number)
values('004','fdx2',3);
insert into numbers(code, ID, number)
values('005','fdx3',4);
insert into numbers(code, ID, number)
values('006','fdx3',4);


My problem is: how to select people that has the same number. For example "Peter" and "Louis".

Answer

By "same number" you mean that there is only one number in numbers for the person. You can do this with group by and having:

select n.id
from numbers n
group by n.id
having min(number) = max(number);

Note: this doesn't take NULL into account. Your question doesn't specify what to do if one of the values is NULL.