How to get ONLY NULL values from table MySQL

I have searched the forum profoundly but I did not see the answer to this specific case. Condiser this data model:

I want to get only Building names where ALL of the rooms have no flowers (where Flower.Name = null).

So If I have for example

Building 1. Room 1. Flower 1

Building 1. Room 1. No flowers (null)

Building 2. Room 1. Flower 1

Building 2. Room 2. Flower 2

Building 3. Room 1. No flowers (null)

Building 3. Room 2. No flowers (null)

I want to see as the result only Building 3.

I asked this query:

SELECT Building.Name, Room.Name, Flower.Name FROM Building 
JOIN Room ON Building.BuildingID = Room.BuildingID 
JOIN Flower ON Room.RoomID = Flower.RoomID 
WHERE Flower.Name IS NULL;

But it lists also Building 1 as the result, as Building 1 has one room without flowers. And what I want to see is only the Buildings with ALL THE ROOMS WITHOUT FLOWERS.

Can someone give me a hint? I am using MySQL on MAMP.

================================== EDITED:

CREATE TABLE Building( 
BuildingID int NOT NULL AUTO_INCREMENT, 
Name varchar(20), 
CONSTRAINT BuildingID_PK PRIMARY KEY(BuildingID));

CREATE TABLE Room(
RoomID int NOT NULL AUTO_INCREMENT,
BuildingID int,
Name varchar(20),
CONSTRAINT RoomID_PK PRIMARY KEY(RoomID),
CONSTRAINT BuildingID_FK FOREIGN KEY (BuildingID) REFERENCES Building(BuildingID));

CREATE TABLE Flower(
FlowerID int NOT NULL AUTO_INCREMENT,
RoomID int,
Name varchar(20),
CONSTRAINT FlowerID_PK PRIMARY KEY(FlowerID),
CONSTRAINT RoomID_FK FOREIGN KEY (RoomID) REFERENCES Room(RoomID));

-- Now data:

INSERT INTO Building (Name) VALUES ("Building 1");
INSERT INTO Building (Name) VALUES ("Building 2");
INSERT INTO Building (Name) VALUES ("Building 3");

INSERT INTO Room(BuildingID, Name) VALUES (1, "Room1");
INSERT INTO Room(BuildingID, Name) VALUES (1, "Room2");
INSERT INTO Room(BuildingID, Name) VALUES (2, "Room1");
INSERT INTO Room(BuildingID, Name) VALUES (2, "Room2");
INSERT INTO Room(BuildingID, Name) VALUES (3, "Room1");
INSERT INTO Room(BuildingID, Name) VALUES (3, "Room2");

INSERT INTO Flower(RoomID, Name) VALUES (1, "Flower 1");
INSERT INTO Flower(RoomID, Name) VALUES (2, null);
INSERT INTO Flower(RoomID, Name) VALUES (3, "Flower 1");
INSERT INTO Flower(RoomID, Name) VALUES (4, "Flower 2");
INSERT INTO Flower(RoomID, Name) VALUES (5, null);
INSERT INTO Flower(RoomID, Name) VALUES (6, null);

I want to have this table with only Building 3 listed:

SELECT Building.Name, Room.Name, Flower.Name FROM Building JOIN Room ON Building.BuildingID = Room.BuildingID JOIN Flower ON Room.RoomID = Flower.RoomID WHERE Flower.Name IS NULL;

Name Name Name

Building 1 Room2 NULL

Building 3 Room1 NULL

Building 3 Room2 NULL

Answer

Here is a possible query:

SELECT Name
FROM Building
WHERE NOT EXISTS
  (SELECT *
   FROM Room JOIN Flower ON Flower.RoomID = Room.RoomID
   WHERE Room.BuildingID = Building.BuildingID
         AND Flower.Name IS NOT NULL);

The query finds all the buildings such that no Room in it exists with a flower which is not null.

Here is the fiddle.

Note that a more usual way of representing this situation is to have in the table Flowers only the rows that corrresponds to rooms with flowers. In this way the query to write is more simple.

Advertisement

Answer