Sql, one-to-many select



  • Hello. Have 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 choose, for example, all houses with red doors without greens.



  • One possibility (through the request made) might look that way.

    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'
             )
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2