CODEWITHSUNDEEP
sqlsqlite
Maxcot
Maxcot

Reputation: 1599

Adding a Query to a Subquery then produces no results

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

Upvotes: 1

Views: 109

Answers (2)

CL.
CL.

Reputation: 180020

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

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

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 |

Upvotes: 1

Related Questions