Dynamic Mysql query Not give right result

Question

i have mysql query like this

SET @sql = NULL;
SELECT

  GROUP_CONCAT(
      'fvalue AS ',x.name
  ) INTO @sql

from reimbursment x
LEFT JOIN reimbursment_limit y
ON y.acc_code = x.acc_code;

SET @sql = CONCAT('SELECT b.firstname, ', @sql, ' from user b
left join reimbursment_limit c
  on b.id = c.user_id
left join reimbursment d
  on c.acc_code = d.acc_code
group by b.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

and table like this,

user

id nip    firstname
1  123456 AAAAA
2  999999 BBBBB

reimbursment

id acc_code name
1  22002    Dentis
2  22004    Allowance

reimbursment limit

id user_id acc_code fvalue
1  1       22002    500000
2  1       22004    700000

and result, like this

firstname Dentis Allowance
AAAAA     500000 500000
BBBBB     null   null

The right result, must

firstname Dentis Allowance
AAAAA     500000 700000
BBBBB     null   null

this query is workig, but not give the right result where the allowance for user AAAAA is must 700000, how fix the result?i have try anything but still not give right result.

Thank you

@devpro : thankyou sory i'm wrong create question, i have been edit my question — Riski Febriansyah, Jan 25 '16 at 07:10

Tah · Answer 1 · 2016-01-25T07:37:04.233

0

The problem is with the join an group by

SET @sql = CONCAT('SELECT b.firstname, ', @sql, ' from user b
left join reimbursement c -- swapped join clause
  on b.id = c.user_id
left join reimbursment_limit d
  on c.acc_code = d.acc_code'); -- removed group by

SQLFiddle

edited Jan 25 '16 at 07:37

answered Jan 25 '16 at 07:19

Tah

1,526
14
22

i can't open your sqlfiddle, no the left join is right i just wrong give record and i have been fixed the right is on b.id = c.user_id – Riski Febriansyah Jan 25 '16 at 07:24
The user_id in reimbursement_limit is both 1. That means both records are tied to user AAAAA – Tah Jan 25 '16 at 07:27
yes, you right user AAAAA must get 500000 and 700000 – Riski Febriansyah Jan 25 '16 at 07:29
no , please see my complete right result in my question – Riski Febriansyah Jan 25 '16 at 07:40
What you're looking for is a pivot table. Please see http://stackoverflow.com/questions/7674786/mysql-pivot-table – Tah Jan 25 '16 at 08:00

Dynamic Mysql query Not give right result

1 Answers1