Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select instances of an entity which are in relation with all other instances of another entity

I have these 3 tables:

CREATE TABLE Fair(
    Title varchar(100),
    Year int,
    PRIMARY KEY(Titolo, Anno),
);

CREATE TABLE Animal(
    ID int PRIMARY KEY,
    Species varchar(100),
    Name varchar(100),
);

CREATE TABLE Participation(
    Fair_Title varchar(100),
    Fair_Year int,
    Animal_ID int,
    FOREIGN KEY(Fair_Title, Fair_Year) references Fair(Title, Year),
    FOREIGN KEY(Animal_ID) references Animale(ID),
    PRIMARY KEY(Fair_Title, Fair_Year, Animal_ID)
);

I have to select all animals which participated in all fairs of 2023.

Currently I am trying to do so like this:

SELECT DISTINCT A.Name as Animal_Name
FROM animal A JOIN participation P ON (A.ID = P.Animal_ID)
WHERE (P.Fair_Year = 2023) AND
NOT EXISTS (
    SELECT F.Title FROM fair F
    WHERE F.Title NOT IN(
        SELECT P1.Fair_Title
        FROM animae A1 JOIN participation P1 ON (A1.ID = P1.ID) 
        WHERE (A1.ID = A.ID AND P1.Fair_Year = 2023) 
    )
);

However this just returns an empty table, even thought the database contains an animal which participates in all 2023 fairs. What am I doing wrong?

SAMPLE DATA:

FAIRS

Title Year
Fair 1 2023),
Fair 2 2023),
Fair 3 2022),
Fair 4 2022);

Animals

ID Species Name
0 Pig Piggo
1 Goat Lucy
2 Cow Maia
3 Cat Melo
4 Dog Dela

Participation

Fair_title Fair_Year Animal_ID
Fair 1 2023 3
Fiera 2 2023 3
Fiera 1 2023 4
Fiera 2 2023 4
Fiera 2 2023 2
Fair 1 2023 2
Fair 2 2022 1
Fair 1 2023 1
Fair 4 2022 1
Fair 4 2022 4
Fair 4 2022 0
Fiera 1 2023 0

Expected results are: Maia, Melo, and Dela, since those are the animals which participated in both 2023 fairs

Tags:

2

Answers


  1. 0

    Get the count of fairs that the animal participated in, and check if it’s the same as the total number of fairs.

    SELECT a.name as animal_name
FROM Animal a
JOIN Participation p ON a.id = p.animal_id
WHERE p.fair_year = 2023
GROUP BY a.id
HAVING COUNT(*) = (SELECT COUNT(*) FROM Fair WHERE year = 2023);

    DEMO

    Login or Signup to reply.
  2. 0

    I don’t have time to write the full answer at the moment, but I can outline the general method. There are two common approaches:

    1. Look for animals where the count of distinct fair participations matches the count of fairs.

    2. Invert the question and look for animals missing at a least one fair. Use this set as a subquery for a NOT EXISTS exclusion, so only animals that didn’t miss any fairs remain.

    Both can options work well.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search