sql - Writing a Complex MySQL Query -

sql - Writing a Complex MySQL Query -

this not duplicate, can find previous question , reply here - mysql: writing complex 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/

you can download db code here: https://www.dropbox.com/s/3gr659y5mk05i5w/tests.sql?dl=0

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

select `words_learned`.`idwords`, words.`idarticle` words_learned inner bring together words on words.idwords = words_learned.`idwords` words_learned.userid = 1 order words_learned.`order` desc

in query, have covered getting articles, means here - first list of articles containing word octopus, , calculation using table words on articles.. should need in order cover rest? please help. .

update

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. } }

i tempted have sub query gets words person has learned , bring together against itself, words group_concat along count. giving:-

octopus, null, 0 dog, "octopus", 1 spoon, "octopus,dog", 2

so sub query like:-

select sub0.idwords, group_concat(sub1.idwords) excl_words, count(sub1.idwords) older_words_cnt words_learned sub0 left outer bring together words_learned sub1 on sub0.userid = sub1.userid , sub0.order_learned < sub1.order_learned sub0.userid = 1 grouping sub0.idwords

giving

idwords excl_words older_words_cnt 1 null 0 2 1 1 3 1,2 2

then bring together results of against other tables, checking articles main idwords matches none of others found.

something (although not tested no test data):-

select sub_words.idwords, words_inc.idarticle ( select sub0.idwords, substring_index(group_concat(sub1.idwords), ',', 10) excl_words, count(sub1.idwords) older_words_cnt words_learned sub0 left outer bring together words_learned sub1 on sub0.userid = sub1.userid , sub0.order_learned < sub1.order_learned sub0.userid = 1 grouping sub0.idwords ) sub_words inner bring together words words_inc on sub_words.idwords = words_inc.idwords left outer bring together words words_exc on words_inc.idarticle = words_exc.idarticle , find_in_set(words_exc.idwords, sub_words.excl_words) words_exc.idwords null order older_words_cnt limit 100

edit - updated exclude articles more 10 words not learned.

select sub_words.idwords, words_inc.idarticle, sub2.idarticle, sub2.count, sub2.content ( select sub0.idwords, group_concat(sub1.idwords) excl_words, count(sub1.idwords) older_words_cnt words_learned sub0 left outer bring together words_learned sub1 on sub0.userid = sub1.userid , sub0.order_learned < sub1.order_learned sub0.userid = 1 grouping sub0.idwords ) sub_words inner bring together words words_inc on sub_words.idwords = words_inc.idwords inner bring together ( select a.idarticle, a.count, a.content, sum(if(c.idwords_learned null, 1, 0)) unlearned_words_count article inner bring together words b on a.idarticle = b.idarticle left outer bring together words_learned c on b.idwords = c.idwords , c.userid = 1 grouping a.idarticle, a.count, a.content having unlearned_words_count < 10 ) sub2 on words_inc.idarticle = sub2.idarticle left outer bring together words words_exc on words_inc.idarticle = words_exc.idarticle , find_in_set(words_exc.idwords, sub_words.excl_words) words_exc.idwords null order older_words_cnt limit 100

edit - effort @ commenting above query:-

this selects columns

select sub_words.idwords, words_inc.idarticle, sub2.idarticle, sub2.count, sub2.content

this sub query gets each of words learnt, along comma separated list of words larger order_learned. particular user id

( select sub0.idwords, group_concat(sub1.idwords) excl_words, count(sub1.idwords) older_words_cnt words_learned sub0 left outer bring together words_learned sub1 on sub0.userid = sub1.userid , sub0.order_learned < sub1.order_learned sub0.userid = 1 grouping sub0.idwords ) sub_words

this articles words (ie, words learned above sub query) used in

inner bring together words words_inc on sub_words.idwords = words_inc.idwords

this sub query gets articles have less 10 words in them not yet learnt particular user.

inner bring together ( select a.idarticle, a.count, a.content, sum(if(c.idwords_learned null, 1, 0)) unlearned_words_count article inner bring together words b on a.idarticle = b.idarticle left outer bring together words_learned c on b.idwords = c.idwords , c.userid = 1 grouping a.idarticle, a.count, a.content having unlearned_words_count < 10 ) sub2 on words_inc.idarticle = sub2.idarticle

this bring together find articles have words in comma separted list 1st sub query (ie words larger order_learned). done left outer bring together want exclude words found (this done in clause checking null)

left outer bring together words words_exc on words_inc.idarticle = words_exc.idarticle , find_in_set(words_exc.idwords, sub_words.excl_words) words_exc.idwords null order older_words_cnt limit 100

mysql sql database select join