How to write a query that gives the names of everyone who does not own a blue house?

60
June 24, 2019, at 11:30 AM

Q. Write a query that gives the names of everyone who does not own a blue house.

Here is my code:

SELECT persons.name
FROM persons
JOIN houses ON (persons.id=houses.owner_id)
WHERE houses.color<>'Blue';

The two tables are shown below:

My result:

Expected result:

How do I keep "Arian" from coming in my results?

"Arian" owns two houses, one red and one blue. Because of the red house he owns, he comes up on my results. I am struggling to figure out how to exclude him.

Answer 1

You can use not exists:

SELECT p.name
FROM persons p
WHERE NOT EXISTS (SELECT 1
                  FROM houses h
                  WHERE h.owner_id = p.id AND
                        h.color = 'Blue'
                 )
Answer 2

We can also use aggregation to solve your problem:

SELECT p.name
FROM person p
INNER JOIN houses h
    ON p.id = h.owner_id
GROUP BY p.name
HAVING SUM(h.color = 'Blue') = 0;

Note that the performance of this approach would be similar to Gordon's answer.

Answer 3

You can use an anti-join:

SELECT persons.name
FROM persons
LEFT JOIN houses
  ON  persons.id   = houses.owner_id
  AND houses.color = 'Blue'
WHERE houses.owner_id IS NULL
READ ALSO
Kaggle kernel training for Facebook MaskRCNN not working properly

Kaggle kernel training for Facebook MaskRCNN not working properly

I am new to Kaggle and Deep Learning and I am trying to train images on Kaggle for segmentationHowever, this is the error I get after training during validation:

130
Create a dictionary with length of elements in a list as keys

Create a dictionary with length of elements in a list as keys

I have a list where there are multiple elements as tuples stored in a list, I want to create a dictionary where the key is length like 1,2 etc and the elements of respective lengthsThe example of list is

58
How to find all &ldquo;Name&rdquo; parameters from big Json data using python3

How to find all “Name” parameters from big Json data using python3

How can I extract all the names from big JSON file using Python3

68
ApolloServer/Mongoose query for finding voted members for poll and sum all the votes

ApolloServer/Mongoose query for finding voted members for poll and sum all the votes

I'm building something like voting app where I have groups (I named them sessions) and I want to check if count of the votes on selected poll is equal to count of the members of the group if it's true then automaticaly sum, find average number and save it to result

64