MySQL Query Using Derived Temp Table And Left Join Against Self

An example (for a personal project) where I’m using a derived temp table (designated ‘x’) and a table joined against itself, used to find the max timestamp. Thanks, Martin!

select tt.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, (count(*)-1) as `calcCount`
	from tbl_todo tt
	left join tbl_todo_stackable tts on tts.fkid = tt.id
	where
		fldEnabled = 1
		and fldRecur in (0, 1)
	group by tts.fkid
	having date(`maxTimeStamp`) <> date(now())
union    
	select tt2.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, 0 as `calcCount`
	from tbl_todo tt2
	left join tbl_todo_stackable tts2 on tts2.fkid = tt2.id
	where
		fldEnabled = 1
		and fldRecur in (0, 1)
	group by tts2.fkid
	having max(fldTimeStamp) = fldCreateTimeStamp
union
	select tt3.*, fkid, fldTimeStamp as `maxTimeStamp`, 0 as `calcCount`
	from tbl_todo tt3
	left join tbl_todo_stackable tts3 on tts3.fkid = tt3.id
	where 
	tts3.pkid_todo_stackable in (
		select 
		pkid_todo_stackable 
		from (
			select s1.pkid_todo_stackable, s1.fkid, s1.fldTimeStamp, s2.fldTimeStamp as date2
			from tbl_todo_stackable s1 
			left join tbl_todo_stackable s2 on s1.fkid = s2.fkid 
			and s1.fldTimeStamp < s2.fldTimeStamp
			where
			s1.fkid in (178, 179)
                        -- hardcoded fkid's, but could use subquery
			having isnull(date2)
                        -- using Martin's tbl join against itself to get max timestamp rows
		) x
	)
	and (datediff(now(), fldCreateTimeStamp)) % 7 = 0
	and date(now()) <> date(fldTimeStamp)
order by fldRecur desc, id;