sql - MySql grouping returns incorrect result -


okay attempting sql magic sadly doesnt work expected. have following query:

    select      `u`.`id` `user_id`,     `athm`.`academy_module_id` `module_id`,     `ahat`.`academy_team_id` `team_id`,     (case         when             ((`act`.`module_id` = `athm`.`academy_module_id`)                 , (`act`.`team_id` = `athm`.`academy_team_id`))                     1         else 0     end) `is_complete`     (((`user` `u`     join `user_has_academy_team` `ahat` on ((`ahat`.`user_id` = `u`.`id`)))     join `academy_team_has_academy_module` `athm` on ((`ahat`.`academy_team_id` = `athm`.`academy_team_id`)))     left outer join `academy_attempt` `act` on ((`act`.`module_id` = `athm`.`academy_module_id`)))

now without grouping returns following result:

user_id, module_id, team_id, is_complete '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '11', '30', '1' '1', '12', '30', '1' '1', '12', '30', '1' '1', '12', '30', '1' '1', '12', '30', '1' '1', '5', '32', '0' '1', '10', '32', '0' '1', '12', '32', '0' '1', '12', '32', '0' '1', '12', '32', '0' '1', '12', '32', '0' '3', '5', '32', '0' '3', '10', '32', '0' '3', '12', '32', '0' '3', '12', '32', '0' '3', '12', '32', '0' '3', '12', '32', '0' '4', '5', '32', '0' '4', '10', '32', '0' '4', '12', '32', '0' '4', '12', '32', '0' '4', '12', '32', '0' '4', '12', '32', '0'

let me explain goal here:

when called module has been taking users in application attempt added academy_attempt. user may take module several times i.e adding more rows academy_attempt table. (as can see user 1 has taken of modules several times).

my goal find out modules has been taking , modules has not been taking.

sadly when group on academy_module_id following result:

user_id, module_id, team_id, is_complete '1', '5', '32', '0' '1', '10', '32', '0' '1', '11', '30', '1' '1', '12', '30', '1'

i guessing because of left outer join since other users not have record inacademy_attempt result not shown.

my question is, there work around when working null data?

here table stuctures:

table: user_has_academy_team columns: user_id int(11) pk academy_team_id int(11) pk 
    table: academy_team_has_academy_module columns: academy_team_id int(11) pk academy_id  int(11) pk academy_module_id   int(11) pk 
    table: academy_attempt columns: id  int(11) ai pk user_id int(11) academy_id  int(11) module_id   int(11) module_type_id  int(11) team_id int(11) score   int(11) medal_id    int(11) pk timestamp   datetime possible_correct    int(11) user_correct    int(11) 
    table: user columns: id  int(11) ai pk username    varchar(100) password    varchar(100) is_active   int(11) user_type_id    int(11) token   varchar(445) organization_id int(11) title_id    int(11) image_path  varchar(100) division_id int(11)

please let me know if need more information


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more