By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE species (
_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE compatibility (
_id INTEGER PRIMARY KEY auto_increment,
speciesA INTEGER,
speciesB INTEGER,
compatibility TINYINT NOT NULL
);
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);
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
| spec |
|---|
| 4 |
| 5 |
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
| spec | name |
|---|---|
| 4 | EspecieD |
| 5 | EspecieD |