Hello. Suppose there are two tables.

create table `houses` ( `id` int unsigned not null auto_increment, `address` varchar(100) not null, primary key(`id`) ); create table `doors` ( `id` int unsigned not null auto_increment, `house_id` int unsigned not null, `color` enum('green','red','yellow') not null, primary key(`id`) );

How to write a request to select, for example, all houses that have red doors and not having green ones.

    1 answer 1

    One of the possible options (via sub-request) may look like this.

     SELECT * FROM houses WHERE id IN (SELECT house_id FROM doors WHERE color = 'red' ) AND id NOT IN (SELECT house_id FROM doors WHERE color = 'green' )
    • @Mike thanks for the clarification, corrected the answer. - cheops
    • Is another query option possible with the same result? - Andrey