代码之家 › 专栏 › 技术社区 › amit sutar

如何在mysql中使用3个表检索数据?

join mysql php

amit sutar · 技术社区 · 6 年前

我有两张桌子:

1.内部员工/主管

  id    employee_name     unique_id
   1     Noah               ABCD
   2     Liam               ABCD
   3     William            ABCD
   4     Benjamin           ABCD
   5     Jacob              EFGH

2.外部员工/主管

  id    name             unique_id
   1    Elijah             ABCD
   2    Ethan              ABCD
   3    Alexander          EFGH

select id
     , employee_name
     , unique_id
  from internal_employee_master
 where unique_id = 'ABCD'
union
select id
     , employee_name
     , unique_id
  from external_employee_master
 where unique_id = 'ABCD'

我想将两名员工的工资单存储到一个表中。我有一张桌子 payslips emp_id 和 emp_type 柱。

工资单 数据如下:

   id     pay_slip        emp_id  emp_type
   1   Noah_payslip.pdf    1     internal
   2   Liam_payslip.pdf    2     internal
   3   Lia_payslip.pdf     1     External

正如您在上表中看到的,我正在存储 emp_id 和 emp_型 属于两个表都以单列形式显示。

现在,我不明白如何拆分内部员工和员工的数据来自 pay_slip

目前,我正在写下面的sql连接来获取 employee_names 属于

$id = $_GET['id];
SELECT ps.id,ps.pdf,ps.emp_id,ps.emp_type,external_employee.name as comemp,
internal_employee.comp_empl_name as comemp
FROM pay_slip as  ps 
INNER JOIN internal_employee_master as internal_employee ON internal_employee.comp_trad_id = ps.trade_id 
INNER JOIN external_employee_master as external_employee ON external_employee.trad_id = ps.trade_id
where ps.is_deleted = 1 AND ps.id = '".$id."'"

请帮助我加入查询以获取 name 和 employee_name 关于 emp_型 类型 工资单 桌子

1 回复 | 直到 6 年前

Andrii Filenko 6 年前

再使用UNION怎么样?

SELECT 
    ps.id,
    ps.pdf,
    ps.emp_id,
    ps.emp_type,
    external_employee.name AS comemp,
    internal_employee.comp_empl_name AS comemp
FROM
    pay_slip AS ps
        INNER JOIN
    internal_employee_master AS internal_employee ON internal_employee.comp_trad_id = ps.trade_id
WHERE
    ps.is_deleted = 1 AND ps.id = '".$id."'
        AND ps.type = 'internal' 
UNION ALL 
SELECT 
    ps.id,
    ps.pdf,
    ps.emp_id,
    ps.emp_type,
    external_employee.name AS comemp,
    internal_employee.comp_empl_name AS comemp
FROM
    pay_slip AS ps
        INNER JOIN
    external_employee_master AS external_employee ON external_employee.trad_id = ps.trade_id
WHERE
    ps.is_deleted = 1 AND ps.id = '".$id."'
        AND ps.type = 'external'

olliefinn 6 年前

SELECT ps.id, ps.pay_slip, ps.emp_type, COALESCE(i.employee_name, e.name) AS name 
FROM payslips ps 
LEFT JOIN internal_employee_master i ON i.id = ps.emp_id AND ps.emp_type = 'internal'
LEFT JOIN external_employee_master e ON e.id = ps.emp_id AND ps.emp_type = 'External' 
AND ps.id = :ID

你可以在这里看到这一点 http://sqlfiddle.com/#!9/53a195/7/0

我要提到的是,在您包含的表和查询中有许多问题。例如,表之间不规则的列名(name vs.employee_name),您已经错过了 is_deleted emp_type 这是令人困惑的一列。