How to get ONLY NULL values from table MySQL

Question

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

Show us some sample table data and the expected result. (As formatted text, no images.) Also read minimal reproducible example. — jarlh
– jarlh, Commented Dec 3, 2019 at 8:28

Renzo · Accepted Answer · 2019-12-03 09:18:29Z

2

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.

answered Dec 3, 2019 at 9:18

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shusang · Accepted Answer · 2019-12-03 09:02:06Z

1

You have try this:

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

answered Dec 3, 2019 at 9:02

Shusang

3371 silver badge10 bronze badges

1 Comment

Dominika Over a year ago

It still shows Building 1 in the answer. I want to see only Building 3.

Ed Bangga · Accepted Answer · 2019-12-03 08:28:10Z

0

Use left join on flower not inner join.

select b.Name, r.Name, f.Name 
from Building b
inner join Room r on r.BuildingID = b.BuildingID
left join Flower f on r.RoomID = f.RoomID
where coalesce(f.Name, '') = ''

answered Dec 3, 2019 at 8:28

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

1 Comment

Dominika Over a year ago

I am still getting Building 1. Room 2 as a result :(

kheengz · Accepted Answer · 2019-12-03 08:56:36Z

0

Left Join will be the solution and not Inner Join

SELECT b.Name, r.Name, f.Name 
FROM Building b
INNER JOIN Room r ON r.BuildingID = b.BuildingID
LEFT JOIN Flower f ON r.RoomID = f.RoomID
WHERE f.Name IS NULL;

answered Dec 3, 2019 at 8:56

kheengz

1,11817 silver badges14 bronze badges

1 Comment

Dominika Over a year ago

I am still getting Building 1. Room 2 as a result.

JrMbh7 · Accepted Answer · 2019-12-03 08:51:33Z

-1

try to write SELECT Building.Name, Room.Name, Flower.Name from flower inner join (building inner join room) where BuildingId = room.BuildingId AND (room.RoomId = flower.RoomId AND flower.Name = '');

answered Dec 3, 2019 at 8:51

JrMbh7

587 bronze badges

2 Comments

Dominika Over a year ago

I get empty set as a result.

JrMbh7 Over a year ago

try to change the names of your variales because is too confusing

Collectives™ on Stack Overflow

How to get ONLY NULL values from table MySQL

5 Answers 5

Comments

1 Comment

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related