时间戳

首页 > 解决方案 > SQL连接五张表

mysql - SQL连接五张表

问题描述

我正在处理问题，但无法解决。

我有五张桌子：

    t_employee = {employee_id_pk, fullname, phone, position, passport}

    t_facility = {facility_id_pk, direction, title}

    t_employee_facility = {emp_fac_id_pk, employee_id_fk, facility_id_fk}  

    this table is @ManyToMany relationship
    between 't_employee' and 't_facility'

    t_transport = {transport_id_pk, type}

    t_trip = {trip_id_pk, trip_code, employee_id_fk, transport_id_fk}

    this table is @ManyToMany relationship between 't_employee' and 't_transport'

我想获得独特的行我尝试过这样的事情

SELECT
  employee_id, fullname, passport, phone, position, direction, title,
  trip_code
FROM
  t_facility
  INNER JOIN t_employee_facility USING (facility_id)
  INNER JOIN t_employee USING (employee_id)
  INNER JOIN t_trip USING (employee_id)
GROUP BY
  employee_id,
  facility_id,
  trip_code

有 8 个唯一行。但是上面的例子给了我 18 行，当我删除时trip_id我得到 8 行，但在这种情况下，我得到trip_code每个employee_id 的重复值。

我只想提取不重复的行。我想，我应该使用INNER JOIN，但我不确定。

是否可以加入所有这些表？

谢谢你

问候

标签： mysqlsqljoin

解决方案

可以使用嵌套连接查询来实现，如下所示：

SELECT B.employee_id, employee_id, fullname, passport, phone,
  position,direction, title, trip_code 
FROM (SELECT A.employee_id, employee_id, fullname, passport, phone,
  position,direction, title  
FROM (SELECT employee_id, direction, title FROM t_facility 
       JOIN t_employee_facility 
         ON t_facility.facility_id = t_employee_facility.facility_id
     ) AS A 
     JOIN employee ON A.employee_id = employee.employee_id) AS B 
JOIN t_trip ON B.employee_id = t_trip.employee_id;

推荐阅读