mysql - 按大小写忽略 LEFT JOIN 查询中的多条记录

Question

我正在尝试使用我的查询来查找与 animal.wild 没有任何关系的特定 Worker.wname 是 TRUE。

例如，从下表我需要返回“Yossi”，因为他没有包含 wild = t 的行

wname |   type    | wild 
-------+-----------+------
 David | Capricorn | f
 David | goat      | f
 David | Lamb      | f
 David | Tiger     | t
 David | wolf      | t
 David | Wolf      | t
 Yossi | Capricorn | f
 Yossi | goat      | f

这是我到目前为止的查询(返回上面的表格):

SELECT
    worker.wname, animal.type, animal.wild
FROM resposibility
LEFT JOIN worker ON resposibility.wid = worker.wid
LEFT JOIN cage ON resposibility.cno = cage.cno
LEFT JOIN animal ON cage.cno = animal.cno
GROUP BY worker.wname, animal.wild, animal.type
ORDER BY worker.wname

Answer 1

SELECT worker.wname
FROM resposibility
LEFT JOIN worker ON resposibility.wid = worker.wid
LEFT JOIN cage ON resposibility.cno = cage.cno
LEFT JOIN animal ON cage.cno = animal.cno
GROUP BY worker.wname
HAVING sum(case when animal.wild = 't' then 1 else 0 end) = 0
ORDER BY worker.wname