Récupérer des lignes dans une base de données SQL entièrement compatibles entre elles

Question

J'ai cette structure simple dans ma base de données

CREATE TABLE species (
  _id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE compatibility (
  _id INTEGER PRIMARY KEY,
  speciesA INTEGER,
  speciesB INTEGER,
  compatibility TINYINT NOT NULL
);

Je tiens à souligner que les espècesA et les espècesB sont composites uniques pour éviter la duplication des informations. Par exemple, si je n'avais que 5 espèces dans ma base de données, le tableau de compatibilité ressemblerait à ceci :

INSERT INTO species VALUES (1, 'EspecieA');
INSERT INTO species VALUES (2, 'EspecieB');
INSERT INTO species VALUES (3, 'EspecieC');
INSERT INTO species VALUES (4, 'EspecieD');
INSERT INTO species VALUES (5, 'EspecieD');

INSERT INTO compatibility VALUES (null, 1, 2, 1);
INSERT INTO compatibility VALUES (null, 1, 3, 1);
INSERT INTO compatibility VALUES (null, 1, 4, 1);
INSERT INTO compatibility VALUES (null, 1, 5, 0);
INSERT INTO compatibility VALUES (null, 2, 3, 1);
INSERT INTO compatibility VALUES (null, 2, 4, 1);
INSERT INTO compatibility VALUES (null, 2, 5, 0);
INSERT INTO compatibility VALUES (null, 3, 4, 1);
INSERT INTO compatibility VALUES (null, 3, 5, 1);
INSERT INTO compatibility VALUES (null, 4, 5, 1);

Je dois écrire une requête qui renvoie une liste d'espèces entièrement compatibles entre elles à partir d'une liste d'espèces donnée, ce qui signifie que toutes les espèces de la liste résultante doivent être compatibles avec toutes les espèces de la liste fournie. Les espèces fournies ne doivent pas apparaître dans la liste des résultats.

J'ai essayé la requête suivante mais elle ne renvoie que les espèces compatibles avec au moins une des espèces fournies :

SELECT id, name
FROM species s
WHERE s.id NOT IN (
    SELECT IF(speciesA NOT IN (1,2,3), speciesA, speciesB) AS specie
    FROM compatibility 
    WHERE (speciesA IN (1,2,3)
    AND compatible IN (0)) OR (speciesB IN (1,2,3)
    AND compatible IN (0))
)
AND s.id NOT IN (1,2,3);

Comment puis-je modifier cette requête pour obtenir une liste d'espèces entièrement compatibles entre elles ?

Pour la requête ci-dessus, le résultat attendu doit être une liste d'espèces contenant uniquement l'espèce 4. Les espèces 1, 2, 3 sont exclues de la liste fournie, et 5 devraient être exclues en raison d'une incompatibilité avec les espèces 1 et 2.

Toute aide ou conseil serait grandement apprécié. Merci!

Answer 1

Nous pouvons résoudre ce problème grâce au filtrage et à l'agrégation.

select spec
from (
    select 
        case when speciesA in (1, 2, 3) then speciesA else speciesB end as ref,
        case when speciesA in (1, 2, 3) then speciesB else speciesA end as spec
    from compatibility c
) c
where ref in (1, 2, 3)
group by spec
having count(*) = 3

La sous-requête

fera référence à l'espèce (clause ref) 和兼容物种 (spec) 放在两个不同的列中。然后，我们可以筛选您感兴趣的三个参考物种，按兼容物种进行分组，最后保留与 having) qui correspond exactement au groupe

.

Si vous souhaitez également connaître le nom de l'espèce, nous pouvons 加入 :

select c.spec, s.name
from (
    select 
        case when speciesA in (1, 2, 3) then speciesA else speciesB end as ref,
        case when speciesA in (1, 2, 3) then speciesB else speciesA end as spec
    from compatibility c
) c
inner join species s on s._id = c.spec
where c.ref in (1, 2, 3)
group by c.spec
having count(*) = 3

Démo DB Fiddle

Answer 2

Cela inclut uniquement les espèces explicitement liées à compatibilité=1 (c'est-à-dire que les espèces sont supposées incompatibles par défaut)

SELECT s._id, s.name
FROM species s
INNER JOIN compatibility c ON
    (s._id = c.speciesA AND c.speciesB IN (1, 2))
    OR (s._id = c.speciesB AND c.speciesA IN (1, 2))
WHERE c.compatibility = 1
AND s._id NOT IN (1, 2)

Cela inclura toutes les espèces sauf celles explicitement associées à compatibilité = 0 (c'est-à-dire que les espèces sont supposées compatibles par défaut)

SELECT s._id, s.name
FROM species s
LEFT JOIN compatibility c ON
    ((s._id = c.speciesA AND c.speciesB IN (1, 2))
    OR (s._id = c.speciesB AND c.speciesA IN (1, 2)))
    AND c.compatibility = 0
WHERE c._id IS NULL
AND s._id NOT IN (1, 2)

Celui qui convient le mieux à votre logique, je vous recommande également de