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个uniquie行。
但是上面的示例给了我18，当我删除trip_id时我得到了8行，但是
在这种情况下，我为每个employee_id重复trip_code值。

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

是否可以联接所有这些表？

谢谢

问候

最佳答案

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

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;

关于mysql - SQL联接五张表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51458355/