mysql - 联接多个表

我有三张桌子。第一个是人员名单：
表

Id_personnel         Name
1                    John
2                    Alice
3                    Tom
4                    Charles
5                    Ben

还有带病假的桌子：
表Personnel

Id_personnel         Department
1                    Department 1
2                    Department 2
3                    Department 3
4                    Department 4
5                    Department 5

对于给定的日期（比如说2012-10-05），我想

Id_personnel         Start_date         End_date
1                    2012-08-02         2012-08-05
2                    2012-08-02         2012-08-15
3                    2012-10-04         2012-10-06
4                    2012-10-04         2012-10-06
5                    2012-09-20         2012-09-21

使用内部连接，我只能让那些病人。我有这样的东西：

Department 1                    John
Department 2                    Alice
Department 3                    Tom             2012-10-04         2012-10-06
Department 4                    Charles         2012-10-04         2012-10-06
Department 5                    Ben

明显的错误结果是：

    SELECT a.Name, b.Department, c.Start_date, c.End_date FROM Personnel a
      INNER JOIN Department b ON a.Id_personnel = b.Id_personnel
      INNER JOIN Medical c ON a.Id_personnel = c.Id_personnel
   WHERE c.Start_date <= 2012-10-05  AND c.End_date >= 2012-10-05

最佳答案

您应该在CASE语句中给出条件，而不是像这样的WHERE子句：

SELECT a.Name, b.Department,
  CASE WHEN c.Start_date <= '2012-10-05' AND c.End_date >= '2012-10-05'
       THEN c.Start_date ELSE '' END AS Start_date
 ,CASE WHEN c.Start_date <= '2012-10-05' AND c.End_date >= '2012-10-05'
       THEN c.End_date ELSE '' END AS END_date
  FROM Personnel a
  JOIN Department b ON a.Id_personnel = b.Id_personnel
  JOIN Medical c ON a.Id_personnel = c.Id_personnel

See this SQLFiddle
更新（来自评论）
如果你不想在同一个部门重复同一个人，你需要这样分组：

GROUP BY a.Id_personnel,Department

See this SQLFiddle
更新2
当同一个人有多个假期时，您需要使用GROUP_CONCAT功能：

SELECT DISTINCT a.Name, b.Department,
  GROUP_CONCAT(CASE WHEN c.Start_date <= '2012-10-05'
                     AND c.End_date >= '2012-10-05'
               THEN c.Start_date ELSE '' END SEPARATOR ' ') AS Start_date
 ,GROUP_CONCAT(CASE WHEN c.Start_date <= '2012-10-05'
                     AND c.End_date >= '2012-10-05'
               THEN c.End_date ELSE '' END SEPARATOR '') AS END_date
  FROM Personnel a
  JOIN Department b ON a.Id_personnel = b.Id_personnel
  JOIN Medical c ON a.Id_personnel = c.Id_personnel
 GROUP BY a.Id_personnel,Department

See this SQLFiddle

see

mysql - 联接多个表