mysql - 检查用户是否存在于其他表中时查询出错

Question

我有一个查询，显示每个修订的修订列表和员工。

现在我试图显示给定员工是否已在答案表中拥有一行。

这是数据库的概述

这是我的工作查询，显示修订和员工列表

SELECT l.id, l.naam, r.id as revision_id, r.beschrijving, e.id as employee_id, e.voornaam, e.achternaam,
FROM lists l
INNER JOIN revisions r ON l.id = r.list_id
INNER JOIN employeelists el ON el.list_id= l.id
INNER JOIN employees e ON e.id = el.employee_id
INNER JOIN customers c ON c.id = e.customer_id
WHERE customer_id = :id AND r.actief = 1

现在我尝试了几种方法来查看员工是否已经在答案表中拥有记录。但是它一直失败..

尝试 1:添加带有左外连接的 Answers 表

SELECT l.id, l.naam, r.id as revision_id, r.beschrijving, e.id as employee_id, e.voornaam, e.achternaam,
**CASE WHEN a.coach_id != 0 THEN 1 ELSE 0 END as FILLED IN**
FROM lists l"""
INNER JOIN revisions r ON l.id = r.list_id
**LEFT OUTER JOIN answers a ON a.revision_id = r.id**
INNER JOIN employeelists el ON el.list_id= l.id
INNER JOIN employees e ON e.id = el.employee_id
INNER JOIN customers c ON c.id = e.customer_id
WHERE customer_id = :id AND r.actief = 1

现在的问题是每个员工都会显示多次......

这是工作数据库的 SQLFiddle，我唯一不能做的就是检查给定的员工 ( werknemer ) 是否存在于答案 ( antwoorden ) 表中。

http://sqlfiddle.com/#!2/0c01c/4

知道如何解决这个问题吗？我尝试了子查询，但也没有成功..谢谢!

立即查询出现问题

我以为我是对的，但还有一个错误。在 answers 表中，显示 werknemer_id (employee_id) = 78 的结果。对于修订版1和2

虽然只有复习 1 的结果(下面的屏幕截图)

谢谢!

Answer 1

这个存在列如果不在 antwoorden 中则为 0，如果存在则为 1

    SELECT l.id, l.naam, r.revisie as revisie, r.id as revisie_id, r.beschrijving, w.id as werknemer, w.voornaam, w.achternaam
    , a.werknemer_id,
(CASE WHEN a.werknemer_id IS NULL THEN 0 ELSE 1 END ) AS `exist`
    FROM lijsten l
    INNER JOIN revisies r ON l.id = r.lijst_id
    INNER JOIN werknemerlijsten wl ON wl.lijst_id = l.id
    INNER JOIN werknemers w ON w.id = wl.werknemer_id
    INNER JOIN klanten k ON k.id = w.klant_id
    LEFT JOIN   antwoorden a ON w.id = a.werknemer_id
    WHERE klant_id = 39 AND r.actief = 1
    GROUP BY  r.beschrijving, w.id

Fiddle