Skip to content
Advertisement

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:

enter image description here

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:

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:

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

Name Name Name

Building 1 Room2 NULL

Building 3 Room1 NULL

Building 3 Room2 NULL

Advertisement

Answer

Here is a possible query:

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement