MySql query joining two table and get combine two row to be one

Question

I have table,

tbl_user

 id uniqid name
 1  123456 AAAA
 2  333333 BBBB

tbl_transaction

 id userid code value
 1  1      2202 500000
 2  1      2204 700000

and How get the result tobe like this

id uniqueid name code1 value1 code2 value2
1  123456   AAAA 2202  500000  2204  700000

in my query i just know to use join this my code

SELECT x.id, x.uniqueid, x.name, y.value
        FROM tbl_user x
        LEFT JOIN tbl_transaction y
            ON y.userid = x.id

I don't know whether you can do that or not but you can use `GROUP_CONCAT` see documentation here: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat — Sagar Guhe, Jan 22 '16 at 09:20
Then you are going to have to construct a pivot query using dynamic MySQL. This is a fairly advanced undertaking. — Tim Biegeleisen, Jan 22 '16 at 09:24
I can give you a query if the number of codes is fixed, but again if it's really variable you need dynamic SQL. — Tim Biegeleisen, Jan 22 '16 at 09:28
@TimBiegeleisen I do not know what the dynamic sql is shaped a text like query or something else? — Riski Febriansyah, Jan 22 '16 at 09:33
Google helped me find this already answered SO question : http://stackoverflow.com/q/12004603/2869791 — Sarath Chandra, Jan 22 '16 at 09:40

Kickstart · Answer 1 · 2016-01-22T10:12:24.690

One way to do it, grouping up the possible values into string using GROUP_CONCAT, and then using SUBSTRING_INDEX to get each value (if it exists).

SELECT x.id, 
        x.uniqueid, 
        x.name, 
        IF(COUNT(y.id) >= 1, SUBSTRING_INDEX(GROUP_CONCAT(y.code ORDER BY y.code), ',', 1), NULL) AS code1,
        IF(COUNT(y.id) >= 1, SUBSTRING_INDEX(GROUP_CONCAT(y.value ORDER BY y.code), ',', 1), NULL) AS value1,
        IF(COUNT(y.id) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.code ORDER BY y.code), ',', 2), ',', -1), NULL) AS code2,
        IF(COUNT(y.id) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.value ORDER BY y.code), ',', 2), ',', -1), NULL) AS value2,
        IF(COUNT(y.id) >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.code ORDER BY y.code), ',', 3), ',', -1), NULL) AS code3,
        IF(COUNT(y.id) >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.value ORDER BY y.code), ',', 3), ',', -1), NULL) AS value3,
        IF(COUNT(y.id) >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.code ORDER BY y.code), ',', 4), ',', -1), NULL) AS code4,
        IF(COUNT(y.id) >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.value ORDER BY y.code), ',', 4), ',', -1), NULL) AS value4,
        IF(COUNT(y.id) >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.code ORDER BY y.code), ',', 5), ',', -1), NULL) AS code5,
        IF(COUNT(y.id) >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(y.value ORDER BY y.code), ',', 5), ',', -1), NULL) AS value5
FROM tbl_user x
LEFT JOIN tbl_transaction y
ON y.userid = x.id
GROUP BY x.id, 
        x.uniqueid, 
        x.name

But you do need to code to cope with the max number of columns of values

score 0 · Accepted Answer · answered Jan 22 '16 at 09:33

0

I really don't know whether it works for u but just give it a try,

SELECT a.id,a.uniqueid,a.name,b.code,b.value,c.code,c.value FROM test.tbl_user as a,test.tbl_transaction as b,test.tbl_transaction as c where a.id=b.userid AND b.code!=c.code limit 1;

answered Jan 22 '16 at 09:33

Sebin Thomas

279
8
21

MySql query joining two table and get combine two row to be one

2 Answers2