A simpler way may be to keep a column in SPECIES missing, if you do not care about it for this species, e.g.:
create table work.species
(species char(10),
can_fly num,
furry num,
levitates num)
;
insert into work.species
values('penguin', 0,0,0)
values('cat', .,1,0)
values('sparrow', 1,0,0)
;
You should then be able to match like this:
create table assigned_observations as
select observations.*,species.species
from observations left join species
on (observations.furry=species.furry or species.furry is null) and
(observations.can_fly=species.can_fly or species.can_fly is null) and
(observations.levitates=species.levitates or species.levitates is null)
;
If you only want the observations that can actually be assigned to a species, just leave out the "left" in the final SQL.
... View more
