0

I have 2 tables with various columns and the tokenid being the same for both. The second table has multiple rows with same tokenid and other columns are different.

Now, I need to select columns from both table where the token id is the same and in the second table, rows with same tokenid must be converted into columns.

Table1:

tokenid   acolumn1   acolumn2   acolumn4
   1       fname1     mname1     lname1
   2       fname2     mname2     lname2

Table2:

id   tokenid   bquestion   banswer
 1       1     questiona   answera
 2       1     questionb   answerb
 3       2     questiona   answera
 4       2     questionb   answerb
 5       3     questionc   answerc

The result should be

tokenid   acolumn1   acolumn2   acolumn3   bquestion1   banswer1   bquestion2   banswer2   bquestion3   banswer3
  1        fname1     mname1     lname1     questiona   answera    questionb    answerb    null           null  
  2        fname2     mname2     lname2     questiona   answera    questionb    answerb    questionc    answerc

I tried querying the second table first with distinct and used it as a subquery to join with the table1. But the database has more than 200,000 rows in table 2 and the match with a tokenid with selection by acolumn1 will produce about 20000 rows result. So my query doesnt complete. is there any optimized way to solve this?

P.S: I'd like to add that I do the queries with php using pdo

shmosel
  • 49,289
  • 6
  • 73
  • 138
Vijai
  • 1,067
  • 2
  • 11
  • 25
  • 2
    Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – shmosel Jan 06 '17 at 06:27
  • And what have you tried so far? – shA.t Jan 06 '17 at 06:33
  • @shA.t like I have said, tried to use LEFT JOIN, using subquery like Select colums from (select columns from .... ). The issue is, i have huge db and most queries fails to load successfully – Vijai Jan 06 '17 at 06:42

1 Answers1

-2

Try this code this joins all columns of your table1 and table2.

Click here to see output 

SELECT table1.tokenid, table1.acol1, table1.acol2, table1.acol3, table2.bquestion, table2.banswer FROM table1
    INNER JOIN table2
    ON table1.tokenid=table2.tokenid
Pavan Baddi
  • 479
  • 1
  • 11
  • 22