CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 2179

SQL: How to join tables right?

Suppose I have 3 tables:

CREATE TABLE animals 
(
    animal_id   INT PRIMARY KEY,
    animal_name VARCHAR(100)
);

CREATE TABLE zoos 
(
     zoo_id   INT PRIMARY KEY,
     zoo_name VARCHAR(100)
);

CREATE TABLE zoo_has_animals 
(
     zoo_id    INT,
     animal_id INT
);

I need to get all empty zoos. I've tried to join them using the next script:

SELECT zoos.zoo_name
FROM zoos
LEFT JOIN zoo_has_animals ON zoos.zoo_id = zoo_has_animals.zoo_id;

But it returns all not empty zoos instead of required empty zoos. What am I doing wrong?

Upvotes: 1

Views: 39

Answers (1)

Lamak
Lamak

Reputation: 70658

No need for a JOIN at all:

SELECT z.zoo_name
FROM zoos as z
WHERE NOT EXISTS(SELECT 1 FROM zoo_has_animals
                 WHERE zoo_id = z.zoo_id);

With a JOIN:

SELECT z.zoo_name
FROM zoos as z
LEFT JOIN zoo_has_animals as h
     ON z.zoo_id = h.zoo_id 
WHERE h.zoo_id IS NULL;

Upvotes: 1

Related Questions