SQL SELECT data between two linked tables is NOT within a specific date range

Question

I have 2 many, many tables and a table to join them.

Employee

• ID
• name

Report

• ID
• performanceDate
• title

report_officer

• officer_id
• REPORT_ID

I want to select all officers who have never been associated with the report or who have not been associated with the report for a certain period of time.

So far I have tried the following (below does not 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 connection request only works when the officer ONLY was connected to the report during a certain timeframe, but it fails when they have several 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 < - sql, , "name" , .

, ?

ORM. ( , ). , -.

Answer 1

. GROUP BY HAVING.

@Iserni, SELECT 0 , SELECT HAVING.

:

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";

, PerformanceDate , , , . , , .

Answer 2

SELECT Officer.*, Report.performanceDate FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id 
LEFT JOIN Report ON Report.id = report_officer.report_id
 AND
   (performanceDate > "2014-03-23 00:00:00" AND
    performanceDate < "2014-04-01 00:00:00")
WHERE Report.id IS NULL

, on .

, group by:

SELECT Officer.id, MAX(Report.performanceDate) FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id 
LEFT JOIN Report ON Report.id = report_officer.report_id
 AND
   (performanceDate > "2014-03-23 00:00:00" AND
    performanceDate < "2014-04-01 00:00:00")
WHERE Report.id IS NULL
GROUP BY Officer.id

, , ( GROUP_CONCAT ).

Update

, , , LEFT JOIN ...

, , - :

SELECT Officer.id as OfficerID, Officer.name,
Report.id as ReportID,
Report.performanceDate

FROM Officer
LEFT JOIN report_officer
  ON Officer.id = report_officer.officer_id 
LEFT JOIN Report
  ON Report.id = report_officer.report_id

WHERE Report.id IS NULL 
OR NOT EXISTS (
    SELECT * FROM report_officer
    INNER JOIN Report ON report_id = Report.id
    WHERE officer_id = Officer.id AND
      performanceDate > "2014-03-23 00:00:00" 
      AND performanceDate < "2014-04-01 00:00:00"
)

, ... , , .

Answer 3

, .

: , , - . , - , .

, " ".

, : (.. , ); LEFT JOIN Officer, , null. , , (, , ).

, ( , ):

SELECT
   Officer.id as OfficerID,
   Officer.name,
   MAX(Report.id) as ReportID,
   MAX(performanceDate) AS performanceDate
FROM Officer
LEFT JOIN report_officer ON (Officer.id = report_officer.officer_id)
LEFT JOIN Report ON (Report.id = report_officer.report_id 
   AND performanceDate BETWEEN 20140323 AND 20140401)
GROUP BY Officer.id, Officer.name
HAVING ReportID IS NULL;

HAVING. HAVING, , , :

SELECT
   Officer.id as OfficerID,
   Officer.name,
   COUNT(Report.id) as reports
FROM Officer
LEFT JOIN report_officer ON (Officer.id = report_officer.officer_id)
LEFT JOIN Report ON (Report.id = report_officer.report_id 
   AND performanceDate BETWEEN 20140323000000 AND 20140401235959)
GROUP BY Officer.id, Officer.name;

, reports 0, . MAX(performanceDate) AS performanceDate, MAX(Report.id) AS ReportID, (, ) , . , .

, YYYYMMDD YYYYMMDD000000, . BETWEEN performanceDate >= '2014-03-23 00:00:00' AND performanceDate <= '2014-04-01 23:59:59'.

Answer 4

AND .

AND performanceDate NOT BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00"

Answer 5

, ...

SELECT *
FROM OFFICER
WHERE ID NOT IN (SELECT OFFICER_ID
                 FROM REPORT_OFFICER)
   OR ID NOT IN (SELECT OFFICER_ID
                 FROM REPORT_OFFICER
                 WHERE performanceDate BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00")

Answer 6

WHERE NOT EXISTS ?

SELECT *
FROM Officer
WHERE NOT EXISTS
  (
    SELECT Report.ID
FROM 
  Report_Officer
   INNER JOIN 
  Report ON 
    Report_Officer.Report_ID = Report.ID
WHERE 
  Report_Officer.Officer_ID = Officer.ID AND
  Report.PerformanceDate BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00"
   )