php mysql左连接多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9538358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql left join with multiple columns
提问by user1245706
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;
如果存在，则删除表mmrl。job;
CREATE TABLE IF NOT EXISTS mmrl.job(
如果不存在则创建表mmrl。job(
jobINT(11) NOT NULL AUTO_INCREMENT ,
jobINT(11) 非空 AUTO_INCREMENT ,
typeVARCHAR(45) NULL ,
typeVARCHAR(45) NULL ,
statusVARCHAR(45) NULL DEFAULT NULL ,
statusVARCHAR(45) NULL 默认 NULL ,
timestampTIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
timestampTIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (job) )
主键 ( job) )
ENGINE = InnoDB
引擎 = InnoDB
DEFAULT CHARACTER SET = latin1;
默认字符集 = latin1;
回答by Julien
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'
to
到
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 JOINsyntax. Move job to the end of your from list and try again.
左连接发生在实际LEFT JOIN语法左侧的表中。将作业移到 from 列表的末尾，然后重试。
FROM patient_has_physician as phys, Scan, Medical_Model as med, job
