MYSQL select and group if column value -

i have mysql table - simplify - 3 columns: name, address, , primary residence.

there can rows duplicate names. want make select statement return person's name , address. if in table twice, means have 2 addresses. however, if 1 of them "primary residence" want return primary residence. otherwise, should return both addresses. thanks!

table:

name | address | primary residence john smith | 123 main str | yes john smith | 456 june str |  mike dore  | 893 west st  | jake john  | 999 east st  | jake john  | 145 south st | 

returns:

name | address john smith | 123 main str mike dore  | 893 west st jake john  | 999 east st jake john  | 145 south st 

here's 1 way...

create table addresses  (name varchar(20) not null ,address varchar(20) not null ,is_primary tinyint null ,primary key (name,address) );  insert addresses values ('john smith','123 main str',1), ('john smith','456 june str',null), ('mike dore','893 west st',null), ('jake john','999 east st',null), ('jake john','145 south st',null);   select * addresses; +------------+--------------+------------+ | name       | address      | is_primary | +------------+--------------+------------+ | jake john  | 145 south st |       null | | jake john  | 999 east st  |       null | | john smith | 123 main str |          1 | | john smith | 456 june str |       null | | mike dore  | 893 west st  |       null | +------------+--------------+------------+  select distinct x.name               , coalesce(y.address,x.address) address            addresses x             left             join addresses y               on y.name = x.name              , y.is_primary = 1; +------------+--------------+ | name       | address      | +------------+--------------+ | jake john  | 145 south st | | jake john  | 999 east st  | | john smith | 123 main str | | mike dore  | 893 west st  | +------------+--------------+