两个相关表之间的SQL SELECT数据不在一定的日期范围内 [英] SQL SELECT data between two related tables NOT within a certain date range
问题描述
官员
- id
- 名称
报告
- id
- performanceDate
- title
report_officer
- officer_id
- report_id
我想选择所有在一段时间内没有与报告关联或没有与报告相关联的官员。
到目前为止，我已经尝试过以下（以下对我来说不起作用）：
SELECT *
FROM Officer
LEFT JOIN report_officer
ON Officer.id = report_officer.officer_id
LEFT JOIN报告
ON Report.id = report_officer.report_id
WHERE（performanceDate IS NULL
OR perf ormanceDate< 2014-03-23 00:00:00
OR performanceDate> 2014-04-01 00:00:00
）
我的左连接查询只有当官员只在某一时间段内与报告相关联时才能工作，但一旦有多个报告，就会失败。
结果：
+ ------------ + ----------------- +
| officer_id | performanceDate |
+ ------------ + ----------------- +
| 130 | NULL | ＃good
| 134 | 2014-03-02 | ＃bad - officer_id 134有一个performanceDate
| 134 | 2014-03-09 | ＃在2014-3-30，我不想在结果中。
| 134 | 2014-03-16 | ＃
| 135 | 2014-03-02 | ＃good
+ ------------ + ----------------- +
SQL Fiddle： http://sqlfiddle.com/#!2/1bf72/3 < - 在sql小提琴中，请参考我正在寻找的列返回的'name'字段。 / p>
有关如何使这项工作有任何想法？
理想情况下，我希望尽可能简单与我的ORM合作。我使用的是原则，而不愿意开始使用完全自定义的代码（所以如果可以只使用连接，那将是很棒的）。我有一个不好的感觉，我需要一个子查询。
感谢大家对此问题的帮助。我的最终解决方案是使用 GROUP BY 和 HAVING 子句。
@Iserni，我不需要 SELECT 在时间范围内有0份报告的人员，我能够 SELECT 所有在时间范围之外的报道的官员或使用 HAVING 的空值报告人员。
这是我的最后代码：
SELECT Officer.id AS OfficerID，Officer.name，Report.id AS ReportID，max（performanceDate）as maxPerfDate FROM官员
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id
LEFT JOIN Report ON Report.id = report_officer.report_id
GROUP BY Officer.id HAVING maxPerfDate为空OR
maxPerfDate < 2014-03-23 00:00:00OR
maxPerfDate> 2014-04-01 00:00:00;
这样做的好处是，我可以利用performanceDate来报告上次的官员提交的时间报告或报告他没有创建报告。所建议的所有其他解决方案都能够删除上次官员创建报告时的有价值信息的能力。
I have 2 many to many tables, and a table to join them.
Officer
- id
- name
Report
- id
- performanceDate
- title
report_officer
- officer_id
- report_id
I want to select all officers that haven't ever been associated with a report or who has not been associated with a report within a certain timeframe.
So far I have tried the following (below doesn't work for me!):
SELECT *
FROM Officer
LEFT JOIN report_officer
ON Officer.id = report_officer.officer_id
LEFT JOIN Report
ON Report.id = report_officer.report_id
WHERE (performanceDate IS NULL
OR performanceDate < "2014-03-23 00:00:00"
OR performanceDate > "2014-04-01 00:00:00"
)
My left join query works only when the officer has ONLY been associated with a report within a certain timeframe, but fails once they have multiple reports.
Result:
+------------+-----------------+
| officer_id | performanceDate |
+------------+-----------------+
| 130 | NULL | # good
| 134 | 2014-03-02 | # bad - officer_id 134 has a performanceDate
| 134 | 2014-03-09 | # on 2014-3-30, I do not want this in the results.
| 134 | 2014-03-16 | #
| 135 | 2014-03-02 | # good
+------------+-----------------+
SQL Fiddle: http://sqlfiddle.com/#!2/1bf72/3 <- in the sql fiddle, please refer to the 'name' field for which columns I am looking to have returned.
Any ideas on how to make this work?
Ideally I would like to make this as simple as possible to work with my ORM. I am using doctrine and would prefer not to start using totally custom code (so if it can be done with only joins, that would be great). I though have a bad feeling I need a sub-query.
Thanks for everyone's help on the problem. My final solution was to use GROUP BY and HAVING clause.
@Iserni, I did not need to SELECT an officer with 0 reports in the timeframe, I was able to SELECT all the officers with reports outside the timeframe or officers with null reports using HAVING.
Here is my final code:
SELECT Officer.id AS OfficerID, Officer.name, Report.id AS ReportID, max(performanceDate) as maxPerfDate FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id
LEFT JOIN Report ON Report.id = report_officer.report_id
GROUP BY Officer.id HAVING maxPerfDate is null OR
maxPerfDate < "2014-03-23 00:00:00" OR
maxPerfDate > "2014-04-01 00:00:00";
The benefit of this is that I can utilize the performanceDate to report when the last time the officer filed a report or report that he never created a report. All the other solutions that were suggested removed the ability to retrieve the valuable information on when the last time the officer created a report.
这篇关于两个相关表之间的SQL SELECT数据不在一定的日期范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
