search

videoP / jaPRO

Community effort to maintain and improve Jedi Academy + Jedi Outcast released by Raven Software
GNU General Public License v2.0
12 stars 6 forks source link

re-add /notcompleted or include it into /dfTodo #8

Closed videoP closed 6 years ago

videoP commented 6 years ago

get list of course-styles sorted by entries select where username does not exist in that that course-style list ?

videoP commented 6 years ago

working query:

SELECT DISTINCT coursename, style, entries FROM LocalRun T WHERE NOT EXISTS ( SELECT * FROM LocalRun WHERE T.style = style AND T.coursename = coursename AND username = 'ark' ) ORDER BY entries DESC

takes like 20 seconds to run, something is wrong.

videoP commented 6 years ago

SELECT T1.coursename, T1.style, T1.entries FROM (SELECT coursename, style,entries FROM LocalRun GROUP BY coursename, style) T1 LEFT JOIN (SELECT coursename, style FROM LocalRun WHERE username = "ark" GROUP BY coursename, style) T2 ON T1.coursename = T2.coursename AND T1.style = T2.style WHERE T2.coursename IS NULL OR T2.style IS NULL ORDER BY entries DESC

Seems to work?

videoP commented 6 years ago

investigate this sometime

[6:29 PM] razor: is join(blah) t2 where t2. is null faster than a where not in select id right join? [6:29 PM] razor: it just seems like the where t2. is null is not idiomatic