mySQL How to return multiple JOIN results as unique columns?

Question

I am querying results from multiple tables based on user_id and specific dates and actions, but I also have a separate table called user_notes:

user_notes

| id |       user_id |                         note |          date |
------------------------------------------------------------------------
|  1 |            10 |                  First note. |    2016-01-01 |
|  2 |            10 |                 Second note. |    2016-01-02 |
|  3 |            11 |                  First note. |    2016-01-03 |
|  4 |            11 |                 Second note. |    2016-01-04 |
|  5 |            11 |                  Third note. |    2016-01-05 |

and I am wondering how do I return all these notes and their dates as separate columns in each record/result when I do joins with other tables and such for which I only return one row per user_id, as the final result would look something like this:

final_result

| user_id |  other_data |       note1 |      date1 |        note2 |      date2 |       note3 |      date3 |...
---------------------------------------------------------------------------------------------------------
|  10     |  .......... | First note. | 2016-01-01 | Second note. | 2016-01-02 |             |            | ...
|  11     |  .......... | First note. | 2016-01-03 | Second note. | 2016-01-04 | Third note. | 2016-01-05 | ...

and so on... there will be up to 10 notes, so there could be up to 10 extra columns for each "note" and "date" entries.

Any idea how this can be accomplished, if at all?

Possible duplicate of [MySQL Join Multiple Rows as Columns](http://stackoverflow.com/questions/15462753/mysql-join-multiple-rows-as-columns) — Kraang Prime, Dec 27 '16 at 20:21
Consider handling issues of data display in application code — Strawberry, Dec 27 '16 at 22:24
I can't - I'm limited to SQL query to get the data into the app. Can't change the app parameters at all. — Crazy Serb, Dec 27 '16 at 22:27

score 0 · Answer 1 · answered Dec 27 '16 at 20:37

0

this can be accomplished by this trick:

select main.user_id, 
  first_note.note as note1, first_note.date,
  second_note.note as note2, second_note.date,
  ...
from user_notes as main
inner join (select * from user_notes where user_id = main.user_id limit 0,1 ) as first_note on main.user_id = first_note.user_id
inner join (select * from user_notes where user_id = main.user_id limit 1,1 ) as second_note on main.user_id = second_note.user_id
...

answered Dec 27 '16 at 20:37

mojtaba zangeneh

141
1
8

Many start at the beginning but some at the at the peak ;) how was the answer? – mojtaba zangeneh Dec 27 '16 at 21:10
Kinda, sorta, but not really... I see where you were going, I am just getting "Unknown column 'main.user_id' in 'where clause'" error, in that inner join condition... somehow the "main" reference is not catching on inside that inner Select. – Crazy Serb Dec 27 '16 at 21:44
Not great, if I'm honest. – Strawberry Dec 27 '16 at 22:25

mySQL How to return multiple JOIN results as unique columns?

1 Answers1