missing dates on query output.

[College_Kid]

I'm using cf 9 on a Windows platform

I can run an SQL statement wherein my students who have been absent for 30 days will appear on this output list. Where I am having an issue is that next to each instructor I would like to show the student's last date of attendance. This somehow I was not showing up when I run my query. The query that I am using is as follows:

SELECT Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, ltfname as instructor, Max(meedate) FROM name LEFT JOIN meeting ON meeStuid = Stuid and meedate BETWEEN NOW() - INTERVAL 31 DAY AND NOW() LEFT JOIN squadlt on squadlt = ltid WHERE meeStuid is null AND  type = 'pupil' AND city = 'jackson' AND status = 'a' AND Stuid <> '432' and Stuid <> '5' and Stuid <> '183'  ORDER BY ltfname, OVER_30_Days

The resulting list below displays as such with null values in the maxdate column . Is there something that I am doing wrong?

[BKBK]

What about something like

and meedate BETWEEN DATEADD(day,-31,NOW()) AND NOW()

[WolfShade]

I don't know what database you're using, but in Oracle, if you do a MAX(anything) without giving it an alias, it just shows up as an undefined column.  Try:

SELECT Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, ltfname as instructor,

     Max(meedate) as meedate

FROM name LEFT JOIN meeting ON meeStuid = Stuid

HTH,

^_^

PS.. it's too late, I'm sure, but I wouldn't use reserved words for table or column names (you named a table "name".)

[EddieLotter]

I suspect you have a problem with your joins. Try changing "name LEFT JOIN meeting" to "name RIGHT JOIN meeting" and see if you get what you expect.

Cheers

Eddie

[BKBK]

You are lucky. Looking again at your query, I am surprised it gave you any result at all. It needs to be improved some more after you apply the suggestions from WolfShade and EddieLotter.

You should:

1) qualify all column names with the respective table name followed by a dot;

2) move the meedate condition to the WHERE clause;

3) avoid givig a table and a column the same name, squadlt.

The same query would then end up looking like the following (I have guessed which columns beloong to which tables):

SELECT name.Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, name.ltfname as instructor, Max(meeting.meedate) as maxMeeDate

FROM name 

LEFT JOIN meeting ON meeting.meeStuid = name.Stuid

LEFT JOIN squadlt ON squadlt.squadlt = name.ltid

WHERE meeting.meeStuid is null

AND meeting.meedate BETWEEN DATEADD(day,-31,NOW()) AND NOW()

AND  name.type = 'pupil'

AND name.city = 'jackson'

AND name.status = 'a'

AND name.Stuid <> '432'

AND name.Stuid <> '5'

AND name.Stuid <> '183' 

ORDER BY name.ltfname, OVER_30_Days

[BKBK]

LEFT JOIN meeting ON meeting.meeStuid = name.Stuid

LEFT JOIN squadlt ON squadlt.squadlt = name.ltid

Then again, one would expect, for a join, something like

LEFT JOIN meeting ON meeting.stuid = name.stuid

LEFT JOIN squadlt ON squadlt.ltid= name.ltid

[College_Kid]

Thanks BKBK,

I spent a few days reviewing my code as well as yours and the other suggestions. The output for each is done correctly however the last date of attendance or max(meedate) still shows up as a empty value. I think this is because my query is set up to find the stuid that does not exist or has a null value within that 30 day date span. Then when i ask mysql to pull the student's most recent date of attendance within the same query it does not include it because to mysql no max(meedate) exist for a stuid that is null anyway for dates that i am specifying. i will try re-writing the entire query differently to see if the output will show both the students who are over 30 days absent from class and their last date of attendance in that class.

[Carl Von Stetten]

This question is also posted to StackOverflow here: http://stackoverflow.com/questions/39082069/missing-dates-on-query-output