sql query to show this result

i need some help to make a query in sql

suppose i have: table name: register

    **id_r   |    name    |    text    |**
        1    |    name 1  |    te开发者_如何学Cxt 1  |
        2    |    name 2  |    text 2  |

and table name: details

**id_d    |    id_r    |    text_d    |**
     1    |      1     |     text 1a  |
     2    |      1     |     text 1b  |
     3    |      2     |     text 2a  |

and i need the result like:

**id_r    |    name    |    text    |     text_d_a    |    text_d_b    |**
  1       |   name 1   |    text 1  |     text 1a     |    text 1b     |
  2       |   name 2   |    text 2  |     text 2a

how can i do the sql statement to have something like this ??

---

It's possible:

SELECT
    R.id_r, R.name, R.[text], 
    D1.text_d AS 'text_d_a', 
    D2.text_d AS 'text_d_b'
FROM 
    register R
    LEFT JOIN details D1 ON R.id_r = D1.id_r AND D1.text_d LIKE '%a%'
    LEFT JOIN details D2 ON R.id_r = D2.id_r AND D2.text_d LIKE '%b%'

---

I think it is bad idea to use this structure, because your query will depends tables data. You can implode text_d values with comma, for example:

SELECT t1.*, 
      (SELECT GROUP_CONCAT(t2.`text_d` SEPARATOR ',') FROM `details` as t2 
       WHERE t2.`id_r`=t1.`id_r`) as `text_d`
FROM `register` as t1