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
精彩评论