php - mysql左连接多列

Question

我正在为我的工作构建一个数据库，但我无法弄清楚如何构建这个查询。

与我的问题相关的表格是:

工作

Surgical_Planning - 将作业作为外键，对某些作业存在，对其他作业不存在

Medical_Model - 工作作为外键，与工作 1 对 1 的关系

这是一个工作查询，我没有任何关于手术计划的信息

    SELECT
    job,
    physician_idphysician as Physician,
    patient_idpatient as Patient,
    status,
    DATE_FORMAT(scan_date, '%M %e, %Y, %l:%i%p') as Scan_Date,
    DATE_FORMAT(timestamp, '%M %e, %Y, %l:%i%p') as Recieved,
    DATE_FORMAT(date_required, '%M %e, %Y, %l:%i%p') as Date_Required
    FROM
    job, patient_has_physician as phys, Scan, Medical_Model as med
    WHERE
    Scan.job_job = job AND phys.job_job = job
    AND med.job_job = job AND job.type =  'medical

我想我想做一个左连接，这样它会按顺序显示每个工作，以及上面查询中的所有信息，但是当有一个工作的 Surgical_Planning 时 # 我希望有一列用于那也是。这是我的尝试，但没有成功

    SELECT
    job,
    physician_idphysician as Physician,
    patient_idpatient as Patient,
    status,
    DATE_FORMAT(scan_date, '%M %e, %Y, %l:%i%p') as Scan_Date,
    DATE_FORMAT(timestamp, '%M %e, %Y, %l:%i%p') as Recieved,
    DATE_FORMAT(date_required, '%M %e, %Y, %l:%i%p') as Date_Required
    FROM
    job, patient_has_physician as phys, Scan, Medical_Model as med

    LEFT JOIN Surgical_Planning ON job.job = Surgical_Planning.job_job
    AND Scan.job_job = job AND phys.job_job = job
    AND med.job_job = job AND job.type = 'medical'

我可以让这个基本的左连接按照我想要的方式工作，但如果我想像上面那样添加更多的列，它就不起作用了。

    SELECT job, planning_id
    FROM job
    LEFT JOIN Surgical_Planning ON job = Surgical_Planning.job_job

也可以使用子查询吗？我可以弄清楚这些更基本的查询，但在处理这些更复杂的连接和子查询时确实遇到了麻烦。任何建议表示赞赏。

编辑 --- 作业表架构

---

-- 表mmrl.job

---

DROP TABLE IF EXISTS mmrl.job ;

如果不存在则创建表 mmrl。job (

job INT(11) NOT NULL AUTO_INCREMENT ,

类型 VARCHAR(45) NULL ,

状态 VARCHAR(45) NULL DEFAULT NULL ,

时间戳 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

主键(工作))

引擎 = InnoDB

默认字符集 = latin1;

Answer 1

改变

LEFT JOIN Surgical_Planning ON job.job = Surgical_Planning.job_job
AND Scan.job_job = job AND phys.job_job = job
AND med.job_job = job AND job.type = 'medical'

到

LEFT JOIN Surgical_Planning ON job.job = Surgical_Planning.job_job
WHERE Scan.job_job = job AND phys.job_job = job
AND med.job_job = job AND job.type = 'medical'

编辑:

左连接发生在实际 LEFT JOIN 语法左侧的表中。将工作移到您的发件人列表的末尾，然后重试。

FROM patient_has_physician as phys, Scan, Medical_Model as med, job