Join different values in a row mysql

Question

The response marked as duplicate does not answer the main question, just answer a question I myself answered during the description of the problem.

I have two tables:

TableInit
+----------+------------------------+---------------------------+
| id_video | titulo                 | desc                      |
+----------+------------------------+---------------------------+
| 23234546 | Inside the White House | As President Donald Trump |
+----------+------------------------+---------------------------+

AND 
TableName
+---------+----------+---------------+--------------+
| meta_id | video_id | upload_key    | upload_value |
+---------+----------+---------------+--------------+
| 64136   | 23234546 | host          | cnn          |
+---------+----------+---------------+--------------+
| 64137   | 23234546 | send          | 0            |
+---------+----------+---------------+--------------+
| 64138   | 23234546 | upload_id     | sHGN-tSNvJYs |
+---------+----------+---------------+--------------+

I would like to return something like this after the query

array(1){[0]=>
  array(15) {
    ["id_video"]=>
    string(7) "23234546"
    ["titulo"]=>
    string(56) "Inside the White House"
    ["desc"]=>
    string(96) "As President Donald Trump."

    array(6){
    ["meta_id"]=>
    string(5) "64136"
    ["video_id"]=>
    string(7) "23234546"
    ["host"]=> 
    string(3) "cnn"
    ["send"]=>
    string(1) "0"
    ["upload_id"] => 'sHGN-tSNvJYs'
    }
  }

 }

or like this

array(1){[0]=>
  array(15) {
    ["id_video"]=>
    string(7) "23234546"
    ["titulo"]=>
    string(56) "Inside the White House"
    ["desc"]=>
    string(96) "As President Donald Trump."
    ["meta_id"]=>
    string(5) "64136"
    ["video_id"]=>
    string(7) "23234546"
    ["host"]=> 
    string(3) "cnn"
    ["send"]=>
    string(1) "0"
    ["upload_id"] => 'sHGN-tSNvJYs'

  }

 }

so I made this query (is this solution correct? or does it have a better option)

SELECT v.*,  if( up.upload_key = 'send', up.upload_value, 0 ) AS send,  
    if( up.upload_key = 'host', up.upload_value, 0 ) AS host, 
     if( up.upload_key = 'upload_id', up.upload_value, 0 ) AS upload_id 
FROM TableInit v JOIN TableName up ON up.video_id =  v.id_video;

The big problem is that this results to me:

| id_video |                 titulo |                      desc | send | host |    upload_id |
|----------|------------------------|---------------------------|------|------|--------------|
| 23234546 | Inside the White House | As President Donald Trump |    0 |  cnn |            0 |
| 23234546 | Inside the White House | As President Donald Trump |    0 |    0 | sHGN-tSNvJYs |
| 23234546 | Inside the White House | As President Donald Trump |    0 |    0 |            0 |

he repeats the values

if I use GROUP BY returns only the values of the first row

| id_video |                 titulo |                      desc | send | host | upload_id |
|----------|------------------------|---------------------------|------|------|-----------|
| 23234546 | Inside the White House | As President Donald Trump |    0 |  cnn |         0 |

when the result that I hope is the join the lines

| id_video |                 titulo |                      desc | send | host |    upload_id |
|----------|------------------------|---------------------------|------|------|--------------|
| 23234546 | Inside the White House | As President Donald Trump |    0 |  cnn | sHGN-tSNvJYs |

How can I get the expected result?

SQL Fiddle SQL Fiddle

You are showing us the expected Laravel output, when actually seeing the expected tabular MySQL output might be more helpful here. Can you include that? Off the top of my head, this is something you should be handling in your PHP presentation layer. — Tim Biegeleisen, Jan 28 '19 at 15:41
I'm not using Laravel and in the example has the expected return from MYSQL — Bruno Andrade, Jan 28 '19 at 15:46
And note that 46 works just as well as a sample id as 23234546 — Strawberry, Jan 28 '19 at 15:46
Look what you have after that part: "when the result that I hope is the join the lines" — Bruno Andrade, Jan 28 '19 at 15:46
[_Automatic pivoter_](http://mysql.rjweb.org/doc.php/pivot). — Rick James, Jan 29 '19 at 20:41

Join different values in a row mysql

0 Answers0