Adding a Query to a Subquery then produces no results

Question

The lastReports subquery by itself, returns 10 rows. The "wrapper" records do exist, but when I join them, I get 0 rows returned in 12ms from: This is on SQLite.

My joins is good, I'm not making a selection mistake mistake(SQL LEFT JOIN Subquery Alias)... what am I missing, or not understanding?

SELECT
    name
FROM
    teachers
INNER JOIN (SELECT
            teacher_id,
            reportingPeriod,
            ReportingType,
            date('now') - 3 as AgeOfReport,
            count(id) as NumberOf
        FROM
            reports
        GROUP BY
            teacher_id
        ORDER BY
            teacher_id ASC,
            reportingPeriod asc
    ) AS lastReports 
ON teachers.id = lastReports.teacher_id;


SAMPLE DATA

TEACHERS
ID NAME 
-----------------
1  Mr John Smith
2  Ms Janet Smith

REPORTS
---------------------------------------------------
ID   TEACHER_ID   REPORTINGPERIOD REPORTINGTYPE
1   1             Jan 2017         Draft
2   1             Feb 2017         Draft
3   2             Jun 2018         Draft
4   2             Jul 2018         Draft
5   1             Mar 2017         Final


DESIRED RESULTS
------------------
Mr John Smith      Final    Mar 2017
Ms Janet Smith     Draft    Jul 2018

Could you provide some sample data and expect result?that really help — D-Shih, Aug 13 '18 at 20:18
Your subquery is off. First of all you should remove the `ORDER BY` clause. Subqueries return a set of data in arbitrary order. Then you group your reports by teacher_id, but you select the reporting period, the reporting type and the age of the report. Which? There are possibly many reports per teacher. — Thorsten Kettner, Aug 13 '18 at 20:22
Ah, but that's exactly the problem I'm trying to solve. The purpose of the subquery is to get the last report of every teacher. There may be 10 reports, the subquery finds the last one for me. — Maxcot, Aug 13 '18 at 20:26
How do you recognize the latest report? By `max(reports.id)`? Then select this per teacher and then join the reports table again matching this. — Thorsten Kettner, Aug 13 '18 at 20:32
No last report is by date. Sometimes people go and "backfill" a missing report. — Maxcot, Aug 13 '18 at 20:33

D-Shih · Accepted Answer · 2018-08-13T20:58:42.117

You can try to make row_number by TEACHER_ID column and order by REPORTINGPERIOD, which mean closer the date in subquery, get rn = 1 the data in each TEACHER_ID the max date and JOIN on TEACHERS.

CREATE TABLE TEACHERS(
  ID INT,
  NAME VARCHAR(50)
);



INSERT INTO TEACHERS VALUES (1, 'Mr John Smith');
INSERT INTO TEACHERS VALUES (2, 'Ms Janet Smith');


CREATE TABLE REPORTS(
  ID INT,
  TEACHER_ID int,
   REPORTINGPERIOD DATE,
  REPORTINGTYPE varchar(100)
);



INSERT INTO REPORTS VALUES (1,1,'Jan 2017', 'Draft');
INSERT INTO REPORTS VALUES (2,1,'Feb 2017', 'Draft');
INSERT INTO REPORTS VALUES (3,2,'Jun 2018', 'Draft');
INSERT INTO REPORTS VALUES (4,2,'Jul 2018', 'Draft');
INSERT INTO REPORTS VALUES (5,1,'Mar 2017', 'Final');

Query 1:

SELECT t.NAME,
       t1.REPORTINGTYPE,
       t1.REPORTINGPERIOD 
FROM teachers as t INNER JOIN
(
    SELECT *,(SELECT COUNT(*) FROM REPORTS tt WHERE tt.TEACHER_ID = t1.TEACHER_ID and tt.REPORTINGPERIOD>=t1.REPORTINGPERIOD) rn
    FROM REPORTS t1
) as t1 on t1.TEACHER_ID = t.id and rn = 1
ORDER BY t.NAME

Results:

|         t.NAME | t1.REPORTINGTYPE | t1.REPORTINGPERIOD |
|----------------|------------------|--------------------|
|  Mr John Smith |            Final |           Mar 2017 |
| Ms Janet Smith |            Draft |           Jun 2018 |

:) Always good to watch an expert in action... thanks. Much appreciated for the teaching too. — Maxcot, Aug 13 '18 at 20:52

score 0 · Answer 2 · answered Aug 15 '18 at 10:34

The REPORTINGPERIOD values do not compare correctly, because Jul comes before Jun in the alphabet. You should use a format like yyyy-mm where the most significant field comes first.

In SQLite, you can simply use MAX() to select entire rows:

SELECT t.Name,
       r.ReportingType,
       max(r.ReportingPeriod)
FROM Teachers t
JOIN Reports r ON t.ID = r.Teacher_ID
GROUP BY r.Teacher_ID;

NAME            REPORTINGTYPE  max(r.ReportingPeriod)
--------------  -------------  ----------------------
Mr John Smith   Final          2017-03               
Ms Janet Smith  Draft          2018-07

Adding a Query to a Subquery then produces no results

2 Answers2

Linked