mysql左连接多列

I'm building a database for my work and I'm having trouble figuring out how to build this query.

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

The tables relevant to my problem are:

与我的问题相关的表是:

job

Surgical_Planning - has job as foreign key, exists for some jobs, doesn't for others

Surgical_Planning - 作为外键的工作,存在于某些工作,不适用于其他工作

Medical_Model - has job as foreign key, 1 to 1 relationship with job

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

This is a working query where I don't have any information about the 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
    WHERE
    Scan.job_job = job AND phys.job_job = job
    AND med.job_job = job AND job.type =  'medical

I think I want to do a Left join so that it will display every job in order, with all the information in the query above, but then when there is a Surgical_Planning for a job # I want there to be a column for that as well. Here is my attempt that is not working

我想我想做一个左连接,这样它就会按顺序显示每个工作,上面的查询中包含所有信息,但是当有一个工作的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'

I can get this basic left join working the way I want, but if I want to add more columns like above it doesn't work.

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

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

could a subquery be used as well? I can figure out these more basic queries but really have trouble with these more complex join and subquery ones. any advice is appreciated.

可以使用子查询吗?我可以找出这些更基本的查询,但真的遇到这些更复杂的连接和子查询问题。任何建议表示赞赏。

EDIT --- Job table schema

编辑---作业表架构

-- Table mmrl.job

- 表mmrl.job

DROP TABLE IF EXISTS mmrl.job ;

DROP TABLE IF EXISTS mmrl.job;

CREATE TABLE IF NOT EXISTS mmrl.job (

CREATE TABLE如果不存在mmrl.job(

job INT(11) NOT NULL AUTO_INCREMENT ,

作业INT(11)NOT NULL AUTO_INCREMENT,

type VARCHAR(45) NULL ,

type VARCHAR(45)NULL,

status VARCHAR(45) NULL DEFAULT NULL ,

status VARCHAR(45)NULL DEFAULT NULL,

timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (job) )

PRIMARY KEY(工作))

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;

1 个解决方案

#1

change

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'

EDIT:

the left join occurs against the table to the left of the actual LEFT JOIN syntax. Move job to the end of your from list and try again.

左连接发生在实际LEFT JOIN语法左侧的表中。将作业移至列表的末尾,然后重试。

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

#1