SQLize Online / PHPize Online  /  SQLtest Online

create table ListTable ( id int, userID int ); create table PeopleTable ( id int, gender char ); insert into ListTable values (1, 1), (2, 2), (3, 3); insert into PeopleTable values (1, 'M'), (2, 'F'); SELECT * FROM ListTable AS list LEFT JOIN PeopleTable AS people ON (list.userID = people.id) WHERE people.gender = 'M'; SELECT COUNT(DISTINCT CASE WHEN people.gender = 'M' THEN list.id END) AS mens, COUNT(DISTINCT CASE WHEN people.gender = 'F' THEN list.id END) AS womens, COUNT(DISTINCT CASE WHEN people.gender IS NULL THEN list.id END) AS unknown FROM ListTable AS list LEFT JOIN PeopleTable AS people ON (list.userID = people.id) ;