SELECT *
FROM plant_detail
LEFT JOIN genus
ON plant_detail.genus = genus.name
LEFT JOIN subfamily
ON plant_detail.subfamily = subfamily.name
LEFT JOIN family
ON plant_detail.family = family.name;
SELECT p.id,
p.binomial_name,
p.genus,
p.family,
p.subfamily,
r.*
FROM plant_detail p
LEFT JOIN genus
ON p.genus = genus.name
LEFT JOIN subfamily
ON p.subfamily = subfamily.name
LEFT JOIN family
ON p.family = family.name
JOIN relations r
ON r.relation_type = 'companion' AND r.to_type = 'family' AND r.to_id = 156 AND
CASE
WHEN r.from_type = 'plant' THEN r.id = p.id
WHEN r.from_type = 'genus' THEN r.id = genus.id
WHEN r.from_type = 'subfamily' THEN r.id = subfamily.id
WHEN r.from_type = 'family' THEN r.id = family.id
END;