mysql query inner join result set issues -
create table if not exists `maf_game_questions` ( `id` int(11) not null auto_increment, `gid` int(11) not null, `qid` int(11) not null, `qtext` text not null, primary key (`id`) ) engine=innodb ; insert `maf_game_questions` (`id`, `gid`, `qid`, `qtext`) values (1, 1, 6, 'click on state has part of rocky mountain range reveal fact agriculture.'), (2, 1, 1, 'click on state borders country see fact agriculture.'), (3, 1, 15, 'find state part of border created river show fact agriculture.'), (4, 1, 14, 'choose state has part of missouri river see fact agriculture.'), (5, 1, 5, 'click on state borders mississippi river see fact agriculture.'), (6, 1, 16, 'click on state panhandle see fact agriculture.'), (7, 1, 8, 'find state part of great plains check out fact agriculture.'), (8, 1, 3, 'select state has bay show fact agriculture.'), (9, 1, 13, 'select state has part of ohio river view fact agriculture.'), (10, 1, 9, 'choose state on atlantic ocean reveal fact agriculture.'), (11, 1, 10, 'select state on pacific ocean see fact agriculture.'), (12, 1, 4, 'choose state on gulf of mexico show fact agriculture.'), (13, 1, 7, 'find state has part of appalachian mountain range see fact agriculture.'), (14, 1, 2, 'find state has peninsula check out fact agriculture.'), (15, 1, 11, 'click on state desert show fact agriculture.'), (16, 1, 12, 'find state borders 1 of great lakes see fact agriculture.'); create table if not exists `maf_game_stats` ( `id` int(11) not null auto_increment, `userid` varchar(100) not null, `sessionid` varchar(100) not null, `gid` int(11) not null, `qid` int(11) not null, `result` varchar(50) not null, `accesstime` datetime not null, primary key (`id`) ) engine=innodb; insert `maf_game_stats` (`id`, `userid`, `sessionid`, `gid`, `qid`, `result`, `accesstime`) values (1, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 6, '0', '2013-04-02 16:26:22'), (2, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 1, '1', '2013-04-02 16:26:27'), (3, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 15, '0', '2013-04-02 16:26:35'), (4, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 14, '1', '2013-04-02 16:26:42'), (5, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 5, '1', '2013-04-02 16:26:51'), (6, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 6, '1', '2013-04-04 16:27:24'), (7, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 16, '1', '2013-04-04 16:27:32'), (8, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 15, '0', '2013-04-04 16:27:38'), (9, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 8, '1', '2013-04-04 16:27:48'), (10, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 3, '1', '2013-04-02 16:31:22'), (11, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 16, '1', '2013-04-02 16:31:28'), (12, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 13, '0', '2013-04-02 16:31:44'), (13, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 9, '0', '2013-04-02 16:31:53'), (14, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 8, '1', '2013-04-02 16:32:00'), (15, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 16, '1', '2013-04-02 16:35:02'), (16, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 10, '0', '2013-04-02 16:35:11'), (17, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 4, '0', '2013-04-02 16:35:17'), (18, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 9, '1', '2013-04-02 16:35:23'), (19, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 4, '0', '2013-04-02 16:35:29'), (20, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 7, '0', '2013-04-03 19:06:12'), (21, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 2, '1', '2013-04-03 19:06:17'), (22, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 11, '0', '2013-04-03 19:06:29'), (23, 'kihyakqtzlsb975', 'ccb4vm744cttrc8is1uuosf8e0', 1, 12, '0', '2013-04-03 19:06:33'), (24, 'kihyakqtzlsb975', 'r0ci652ln8j1oqhvjd88s0is34', 1, 5, '0', '2013-04-05 09:47:22'), (25, 'kihyakqtzlsb975', 'r0ci652ln8j1oqhvjd88s0is34', 1, 2, '1', '2013-04-05 09:47:28'), (26, 'kihyakqtzlsb975', 'r0ci652ln8j1oqhvjd88s0is34', 1, 14, '0', '2013-04-05 09:47:37'), (27, 'kihyakqtzlsb975', 'r0ci652ln8j1oqhvjd88s0is34', 1, 9, '1', '2013-04-05 09:47:43'), (28, 'kihyakqtzlsb975', 'r0ci652ln8j1oqhvjd88s0is34', 1, 12, '0', '2013-04-05 09:47:50'); select a.gid, a.qid, q.qtext, sum(a.result = 1) first_attempt_correct, sum(a.result = 0) first_attempt_incorrect, c.all_attempt_correct, c.all_attempt_incorrect maf_game_stats inner join ( select userid, gid, qid, min(accesstime) min_date maf_game_stats group userid, gid, qid ) b on a.userid = b.userid , a.gid = b.gid , a.qid = b.qid , a.accesstime = b.min_date inner join ( select gid, qid, sum(result = 1) all_attempt_correct, sum(result = 0) all_attempt_incorrect maf_game_stats group gid, qid ) c on a.gid = c.gid , a.qid = c.qid inner join maf_game_questions q on a.qid = q.qid , a.gid = q.gid a.gid ='1' , a.accesstime >= '2013-04-05' , a.accesstime < '2013-04-05' + interval 1 day group a.gid, a.qid q: ineed result below given result set useing 2013-04-02 2013-04-05 works fine same day 2013-04-05 2013-04-05 not getting result ? idea please here sqlfidler :
http://sqlfiddle.com/#!2/181a2/1
------------------------ qtext 1st attempt correct 1st attempt incorrect attempt correct attempt incorrect ---------- --------------------- ---------------------- -------------------- --------------------------- question 0 0 0 1 question 0 0 0 1 question 0 0 0 0 question 0 0 0 0 question 0 0 1 0
is you're looking -- move where criteria join. problem above query your're joining on min(accesstime) , no records come 2013-04-05 within min results.
select * maf_game_stats inner join ( select userid, gid, qid, min(accesstime) min_date maf_game_stats accesstime >= '2013-04-05' , accesstime < '2013-04-06' group userid, gid, qid ) b on a.userid = b.userid , a.gid = b.gid , a.qid = b.qid , a.accesstime = b.min_date inner join ( select gid, qid, sum(result = 1) all_attempt_correct, sum(result = 0) all_attempt_incorrect maf_game_stats group gid, qid ) c on a.gid = c.gid , a.qid = c.qid inner join maf_game_questions q on a.qid = q.qid , a.gid = q.gid a.gid ='1' group a.gid, a.qid
Comments
Post a Comment