sql - Complex MySql Query is giving incorrect results -

sql - Complex MySql Query is giving incorrect results -

this not duplicate, can find previous question , reply here. deep testing on proved previous reply incorrect. - writing complex mysql query

i have 3 tables.

table words_learned contains words known user, , order in words learned. has 3 columns 1) word id , 2)user id , 3) order in word learned.

table article contains articles. has 3 columns 1) article id, 2) unique word count , 3) article contents.

table words contains list of unique words contained in each article. has 2 columns 1) word id , 2) article id

the database diagram below/

now, using database , using "only" mysql, need below work.

given user id, should list of words known user, sorted in revese order learned. in other words, learned words @ top of list.

let’s query on user id shows they’ve memorized next 3 words, , track order in they’ve learned words. octopus - 3 dog - 2 spoon - 1

first list of articles containing word octopus, , calculation using table words on articles. calculation means if article contains more 10 words not appear in user’s vocabulary list (pulled table words_learned), excluded listing.

then, query records contain dog, not contain “octopus”

then, query records contain spoon, not contain words octopus or dog

and maintain doing repetitive process until we’ve found 100 records meet criteria.

to accomplish process, did below (please visit sqlfiddle link see table structures, test info , query)

http://sqlfiddle.com/#!2/48dae/1

in query, can see generated results , invalid. on "proper query", result should be,

level 1 level 1 level 1 level 2 level 2 level 2 level 3 level 3

here phudocode improve understanding.

do while articles found < 100 { each ($x known words, in order words learned) { select articles contain word $x, 1) article has not been included in previous loops, , 2)where count of "unknown" words less 10. maintain these articles in order. } }

please help.

select * ( select a.idarticle, a.content, max(`order`) max_order words_learned wl bring together words w on w.idwords = wl.idwords bring together article on a.idarticle = w.idarticle wl.userid = 4 grouping a.idarticle ) left bring together ( select count(*) unknown_count, w2.idarticle words w2 left bring together words_learned wl2 on wl2.idwords = w2.idwords , wl2.userid = 4 wl2.idwords null grouping w2.idarticle ) unknown_counts on unknown_counts.idarticle = a.idarticle unknown_count null or unknown_count < 10 order max_order desc limit 100

http://sqlfiddle.com/#!2/6944b/9

the first derived table selects unique articles given user knows 1 or more words maximum order value of words. maximum order value used sort final results articles containing high order words appear first.

the sec derived table counts number of words given user doesn't know each article. table used exclude articles contain 10 or more words user doesn't know.

mysql sql database join indexing